Results 1 to 9 of 9

Thread: Extract Data to Various Cells of Unstructured Data

  1. #1
    Acolyte mihir777's Avatar
    Join Date
    May 2016
    Posts
    23
    Articles
    0
    Excel Version
    2016

    Extract Data to Various Cells of Unstructured Data



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Dear All,

    Sheet attched for reference but need formula so that it can be auto filed

    Extract Data to Various Cells of Unstructured Data


    Content SO Date S Product Cust ID Customer Name Batch No Product Segment #Cases File Status End Date Amount to be considered Email ID Count Due Date
    Dear Team,

    We have received allocation



    *SO*

    2632

    *Date*

    24-Aug-21

    *S Product*

    SC

    *Cust ID*

    123

    *Customer Name*

    Raj

    *Batch No*

    411

    *Product*

    CC

    *Segment*

    BKT-3

    *# Cases*

    849

    *File Status*

    Active

    *End Date*

    10-Sep-21

    *Amount to be considered*

    TOS

    *Email ID Count*

    -

    *Due Date*

    12-Sep-21
    2632 24-Aug-21 SC Raj 411 CC BKT-3 849 Active 10-Sep-21 TOS - 12-Sep-21
    Attached Files Attached Files

  2. #2
    Conjurer alansidman's Avatar
    Join Date
    Oct 2018
    Location
    Steamboat Springs
    Posts
    343
    Articles
    0
    Excel Version
    O365
    With Power Query

    Code:
    let
        Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
        #"Removed Other Columns" = Table.SelectColumns(Source,{"Column1"}),
        #"Promoted Headers" = Table.PromoteHeaders(#"Removed Other Columns", [PromoteAllScalars=true]),
        #"Split Column by Delimiter" = Table.SplitColumn(#"Promoted Headers", "Content", Splitter.SplitTextByDelimiter("#(lf)", QuoteStyle.Csv), {"Content.1", "Content.2", "Content.3", "Content.4", "Content.5", "Content.6", "Content.7", "Content.8", "Content.9", "Content.10", "Content.11", "Content.12", "Content.13", "Content.14", "Content.15", "Content.16", "Content.17", "Content.18", "Content.19", "Content.20", "Content.21", "Content.22", "Content.23", "Content.24", "Content.25", "Content.26", "Content.27", "Content.28", "Content.29", "Content.30", "Content.31", "Content.32", "Content.33", "Content.34", "Content.35", "Content.36", "Content.37", "Content.38", "Content.39", "Content.40", "Content.41", "Content.42", "Content.43", "Content.44", "Content.45", "Content.46", "Content.47", "Content.48", "Content.49", "Content.50", "Content.51", "Content.52", "Content.53", "Content.54", "Content.55", "Content.56", "Content.57", "Content.58", "Content.59", "Content.60", "Content.61"}),
        #"Transposed Table" = Table.Transpose(#"Split Column by Delimiter"),
        #"Filtered Rows" = Table.SelectRows(#"Transposed Table", each ([Column1] <> "" and [Column1] <> " We have received  allocation" and [Column1] <> "Dear Team,")),
        #"Changed Type3" = Table.TransformColumnTypes(#"Filtered Rows",{{"Column1", type text}}),
        #"Added Custom" = Table.AddColumn(#"Changed Type3", "Custom", each if Text.Contains([Column1],"*") then [Column1] else null),
        #"Filled Down" = Table.FillDown(#"Added Custom",{"Custom"}),
        #"Added Custom1" = Table.AddColumn(#"Filled Down", "Custom.1", each if [Column1] = [Custom] then 1 else 2),
        #"Filtered Rows1" = Table.SelectRows(#"Added Custom1", each ([Custom.1] = 2)),
        #"Pivoted Column" = Table.Pivot(#"Filtered Rows1", List.Distinct(#"Filtered Rows1"[Custom]), "Custom", "Column1"),
        #"Removed Columns" = Table.RemoveColumns(#"Pivoted Column",{"Custom.1"})
    in
        #"Removed Columns"
    Attached Files Attached Files

  3. #3
    Acolyte mihir777's Avatar
    Join Date
    May 2016
    Posts
    23
    Articles
    0
    Excel Version
    2016

    require it to be fetched from content

    fetch from content column

    Quote Originally Posted by alansidman View Post
    With Power Query

    Code:
    let
        Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
        #"Removed Other Columns" = Table.SelectColumns(Source,{"Column1"}),
        #"Promoted Headers" = Table.PromoteHeaders(#"Removed Other Columns", [PromoteAllScalars=true]),
        #"Split Column by Delimiter" = Table.SplitColumn(#"Promoted Headers", "Content", Splitter.SplitTextByDelimiter("#(lf)", QuoteStyle.Csv), {"Content.1", "Content.2", "Content.3", "Content.4", "Content.5", "Content.6", "Content.7", "Content.8", "Content.9", "Content.10", "Content.11", "Content.12", "Content.13", "Content.14", "Content.15", "Content.16", "Content.17", "Content.18", "Content.19", "Content.20", "Content.21", "Content.22", "Content.23", "Content.24", "Content.25", "Content.26", "Content.27", "Content.28", "Content.29", "Content.30", "Content.31", "Content.32", "Content.33", "Content.34", "Content.35", "Content.36", "Content.37", "Content.38", "Content.39", "Content.40", "Content.41", "Content.42", "Content.43", "Content.44", "Content.45", "Content.46", "Content.47", "Content.48", "Content.49", "Content.50", "Content.51", "Content.52", "Content.53", "Content.54", "Content.55", "Content.56", "Content.57", "Content.58", "Content.59", "Content.60", "Content.61"}),
        #"Transposed Table" = Table.Transpose(#"Split Column by Delimiter"),
        #"Filtered Rows" = Table.SelectRows(#"Transposed Table", each ([Column1] <> "" and [Column1] <> " We have received  allocation" and [Column1] <> "Dear Team,")),
        #"Changed Type3" = Table.TransformColumnTypes(#"Filtered Rows",{{"Column1", type text}}),
        #"Added Custom" = Table.AddColumn(#"Changed Type3", "Custom", each if Text.Contains([Column1],"*") then [Column1] else null),
        #"Filled Down" = Table.FillDown(#"Added Custom",{"Custom"}),
        #"Added Custom1" = Table.AddColumn(#"Filled Down", "Custom.1", each if [Column1] = [Custom] then 1 else 2),
        #"Filtered Rows1" = Table.SelectRows(#"Added Custom1", each ([Custom.1] = 2)),
        #"Pivoted Column" = Table.Pivot(#"Filtered Rows1", List.Distinct(#"Filtered Rows1"[Custom]), "Custom", "Column1"),
        #"Removed Columns" = Table.RemoveColumns(#"Pivoted Column",{"Custom.1"})
    in
        #"Removed Columns"
    Attached Files Attached Files

  4. #4
    Conjurer alansidman's Avatar
    Join Date
    Oct 2018
    Location
    Steamboat Springs
    Posts
    343
    Articles
    0
    Excel Version
    O365
    Is there a question here? Are you satisfied with the code? Is this solved?

  5. #5
    Acolyte mihir777's Avatar
    Join Date
    May 2016
    Posts
    23
    Articles
    0
    Excel Version
    2016
    No Still i am facing difficulties as every day there are multiple entries so i require a steady formula so that i can copy paste it as and when required.

  6. #6
    Conjurer alansidman's Avatar
    Join Date
    Oct 2018
    Location
    Steamboat Springs
    Posts
    343
    Articles
    0
    Excel Version
    O365
    If you convert your raw data to a table, when you use Power Query, and add additional data to the table and click on Refrresh All then the query updates for the new data. Suggest you look at this tutorial.

    https://www.youtube.com/watch?v=iQFChw-MFcw

    or/and

    https://www.youtube.com/watch?v=L4BuUzccLpo

  7. #7
    Acolyte mihir777's Avatar
    Join Date
    May 2016
    Posts
    23
    Articles
    0
    Excel Version
    2016
    received this error , i am using excel 2016

    Click image for larger version. 

Name:	Untitled.png 
Views:	10 
Size:	29.7 KB 
ID:	10605

  8. #8
    Conjurer alansidman's Avatar
    Join Date
    Oct 2018
    Location
    Steamboat Springs
    Posts
    343
    Articles
    0
    Excel Version
    O365
    Cannot diagnose the issue without seeing your file and code that contains the error message. Want to upload it for analysis?

  9. #9
    Super Moderator p45cal's Avatar
    Join Date
    Dec 2012
    Posts
    2,023
    Articles
    0
    Excel Version
    365
    Quote Originally Posted by mihir777 View Post
    received this error , i am using excel 2016
    remove:
    Code:
    , [PromoteAllScalars=true]
    in the Promoted Headers step (it's the last argument in the brackets).
    It's down to version differences; although this will get you past that step there's no guarantee there won't be more hiccups.
    Try to update your Power Query version to a more recent version.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •