Extract Data to Various Cells of Unstructured Data

M

mihir777

New member
Joined
May 28, 2016
Messages
24
Reaction score
0
Points
1
Excel Version(s)
2016
Dear All,

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

Extract Data to Various Cells of Unstructured Data


ContentSODateS ProductCust IDCustomer NameBatch NoProductSegment#CasesFile StatusEnd DateAmount to be consideredEmail ID CountDue 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		263224-Aug-21SC Raj411CCBKT-3849Active10-Sep-21TOS-12-Sep-21
 

Attachments

  • formula.xlsx
    9.8 KB · Views: 3
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"
 

Attachments

  • formula.xlsx
    28.4 KB · Views: 3
require it to be fetched from content

fetch from content column

alansidman said:
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"
Click to expand...
 

Attachments

  • formula (1).xlsx
    16.9 KB · Views: 4
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.
 
received this error , i am using excel 2016

Untitled.png
 
Cannot diagnose the issue without seeing your file and code that contains the error message. Want to upload it for analysis?
 
mihir777 said:
received this error , i am using excel 2016
Click to expand...
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.
 
You must log in or register to reply here.
Back
Top