Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi all,
Could you please help me with the following scenario?
I have data imported from our ERP with columns for invoice nr, product id and product name. The problem is that the order nr appears in the ProductName column and I have nulls every time this happens. I would like to split the ProductName into 2 new columns: one for Order Nr and one for Order Date and then fill down for each invoice.
Here's a snapshot of how my data looks like:
InvoiceNr. | ProductID | ProductName |
1 | Order 25 of 01.01.2019 | |
1 | A1 | Product A |
1 | B1 | Product B |
1 | C1 | Product C |
2 | Order 30 of 02.01.2019 | |
2 | A1 | Product A |
2 | B1 | Product B |
3 | C1 | Product C |
3 | D1 | Product D |
3 | E1 | Product E |
4 | Order 31 of 02.01.2019 | |
4 | C1 | Product C |
Solved! Go to Solution.
Here is one way to do it in the query editor. To see how it works, just create a blank query, go to Advanced Editor, and replace the text there with the M code below.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUVIAYv+ilNQiBSNThfw0BQNDPSAyMjC0VIrVgShxBBEBRfkppcklCo5wYSdkYSe4sDOysDNY2AjFHmMDsD1GKPYYYbfHCLs9xtjtAQm7IAu7wIVdkYVdwcImqK4yxOIqEyz2xAIA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"InvoiceNr." = _t, ProductID = _t, ProductName = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"InvoiceNr.", Int64.Type}, {"ProductID", type text}, {"ProductName", type text}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each if Text.Contains([ProductName], "Order") then [ProductName] else null),
#"Filled Down" = Table.FillDown(#"Added Custom",{"Custom"}),
#"Filtered Rows" = Table.SelectRows(#"Filled Down", each ([ProductID] <> " ")),
#"Split Column by Delimiter" = Table.SplitColumn(#"Filtered Rows", "Custom", Splitter.SplitTextByEachDelimiter({" of "}, QuoteStyle.Csv, false), {"Custom.1", "Custom.2"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Custom.1", type text}, {"Custom.2", type date}}),
#"Extracted Text After Delimiter" = Table.TransformColumns(#"Changed Type1", {{"Custom.1", each Text.AfterDelimiter(_, "Order "), type text}}),
#"Renamed Columns" = Table.RenameColumns(#"Extracted Text After Delimiter",{{"Custom.1", "OrderNumber"}, {"Custom.2", "OrderDate"}})
in
#"Renamed Columns"
Regards,
Pat
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
Place the following M code in a blank query to see the steps.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUVIAYv+ilNQiBSNThfw0BQNDPSAyMjC0VIrVgShxBBEBRfkppcklCo5wYSdkYSe4sDOysDNY2AjFHmMDsD1GKPYYYbfHCLs9xtjtAQm7IAu7wIVdkYVdwcImqK4yxOIqEyz2xAIA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"InvoiceNr." = _t, ProductID = _t, ProductName = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"InvoiceNr.", Int64.Type}, {"ProductID", type text}, {"ProductName", type text}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "NameOrder", each if [ProductID] = " " then Text.Replace(Text.Start([ProductName], Text.PositionOf([ProductName], "of ", Occurrence.First)-1), "Order ", "") else null, Int64.Type),
#"Added Custom1" = Table.AddColumn(#"Added Custom", "OrderDate", each if [ProductID] = " " then Text.End([ProductName],10) else null, type date),
#"Filled Down" = Table.FillDown(#"Added Custom1",{"NameOrder", "OrderDate"}),
#"Removed Columns" = Table.RemoveColumns(#"Filled Down",{"ProductName"}),
#"Filtered Rows" = Table.SelectRows(#"Removed Columns", each ([ProductID] <> " "))
in
#"Filtered Rows"
Please mark the question solved when done and consider giving a thumbs up if posts are helpful.
Contact me privately for support with any larger-scale BI needs, tutoring, etc.
Cheers
try this:
let
Source = Table.FromRecords(Json.Document(Binary.Decompress(Binary.FromText("i65W8swry89MTvUr0lOyMtRRCijKTylNLvF0UbJSUlCC8/0Sc1OBIv5FKalFCkamCvlpCgaGekBkZGBoqVSrg9ccR0MMg6A8BUdCep1w63UipNcZt15nDL1GxPnd2ADsdyOcfjeiwO9GFPjdmAK/o+l1wa3XhZBeV9x6XTH0mhAZ5oYEwtyEFL/HAgA=",BinaryEncoding.Base64),Compression.Deflate))),
rows = Table.ToRows(Source)&{{""," "}},
acc = List.Accumulate(
rows,
{"", {}, {}, {}},
(s,c)=>if s{0}<>" " and c{1}?=" "
then {c{1}?, List.Skip(Splitter.SplitTextByEachDelimiter({"Order ", " of "})(c{2}?)), {}, s{3}&{s{2}}}
else {c{1}?, s{1}, s{2}&{c&s{1}}, s{3}}),
result = Table.FromRows(List.Combine(acc{3}), Table.ColumnNames(Source)&{"Order", "Date"})
in
result
try this:
let
Source = Table.FromRecords(Json.Document(Binary.Decompress(Binary.FromText("i65W8swry89MTvUr0lOyMtRRCijKTylNLvF0UbJSUlCC8/0Sc1OBIv5FKalFCkamCvlpCgaGekBkZGBoqVSrg9ccR0MMg6A8BUdCep1w63UipNcZt15nDL1GxPnd2ADsdyOcfjeiwO9GFPjdmAK/o+l1wa3XhZBeV9x6XTH0mhAZ5oYEwtyEFL/HAgA=",BinaryEncoding.Base64),Compression.Deflate))),
rows = Table.ToRows(Source)&{{""," "}},
acc = List.Accumulate(
rows,
{"", {}, {}, {}},
(s,c)=>if s{0}<>" " and c{1}?=" "
then {c{1}?, List.Skip(Splitter.SplitTextByEachDelimiter({"Order ", " of "})(c{2}?)), {}, s{3}&{s{2}}}
else {c{1}?, s{1}, s{2}&{c&s{1}}, s{3}}),
result = Table.FromRows(List.Combine(acc{3}), Table.ColumnNames(Source)&{"Order", "Date"})
in
result
Place the following M code in a blank query to see the steps.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUVIAYv+ilNQiBSNThfw0BQNDPSAyMjC0VIrVgShxBBEBRfkppcklCo5wYSdkYSe4sDOysDNY2AjFHmMDsD1GKPYYYbfHCLs9xtjtAQm7IAu7wIVdkYVdwcImqK4yxOIqEyz2xAIA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"InvoiceNr." = _t, ProductID = _t, ProductName = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"InvoiceNr.", Int64.Type}, {"ProductID", type text}, {"ProductName", type text}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "NameOrder", each if [ProductID] = " " then Text.Replace(Text.Start([ProductName], Text.PositionOf([ProductName], "of ", Occurrence.First)-1), "Order ", "") else null, Int64.Type),
#"Added Custom1" = Table.AddColumn(#"Added Custom", "OrderDate", each if [ProductID] = " " then Text.End([ProductName],10) else null, type date),
#"Filled Down" = Table.FillDown(#"Added Custom1",{"NameOrder", "OrderDate"}),
#"Removed Columns" = Table.RemoveColumns(#"Filled Down",{"ProductName"}),
#"Filtered Rows" = Table.SelectRows(#"Removed Columns", each ([ProductID] <> " "))
in
#"Filtered Rows"
Please mark the question solved when done and consider giving a thumbs up if posts are helpful.
Contact me privately for support with any larger-scale BI needs, tutoring, etc.
Cheers
Here is one way to do it in the query editor. To see how it works, just create a blank query, go to Advanced Editor, and replace the text there with the M code below.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUVIAYv+ilNQiBSNThfw0BQNDPSAyMjC0VIrVgShxBBEBRfkppcklCo5wYSdkYSe4sDOysDNY2AjFHmMDsD1GKPYYYbfHCLs9xtjtAQm7IAu7wIVdkYVdwcImqK4yxOIqEyz2xAIA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"InvoiceNr." = _t, ProductID = _t, ProductName = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"InvoiceNr.", Int64.Type}, {"ProductID", type text}, {"ProductName", type text}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each if Text.Contains([ProductName], "Order") then [ProductName] else null),
#"Filled Down" = Table.FillDown(#"Added Custom",{"Custom"}),
#"Filtered Rows" = Table.SelectRows(#"Filled Down", each ([ProductID] <> " ")),
#"Split Column by Delimiter" = Table.SplitColumn(#"Filtered Rows", "Custom", Splitter.SplitTextByEachDelimiter({" of "}, QuoteStyle.Csv, false), {"Custom.1", "Custom.2"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Custom.1", type text}, {"Custom.2", type date}}),
#"Extracted Text After Delimiter" = Table.TransformColumns(#"Changed Type1", {{"Custom.1", each Text.AfterDelimiter(_, "Order "), type text}}),
#"Renamed Columns" = Table.RenameColumns(#"Extracted Text After Delimiter",{{"Custom.1", "OrderNumber"}, {"Custom.2", "OrderDate"}})
in
#"Renamed Columns"
Regards,
Pat
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
Thank you very much, Pat. It worked like a charm 🙂
Happy Holidays
Hi @AlB,
Thank you for your reply.
Here's how I would like the end result to be:
InvoiceNr. | ProductID | ProductName | Order | Order Date |
1 | A1 | Product A | 25 | 01.01.2019 |
1 | B1 | Product B | 25 | 01.01.2019 |
1 | C1 | Product C | 25 | 01.01.2019 |
2 | A1 | Product A | 30 | 02.02.2019 |
2 | B1 | Product B | 30 | 02.02.2019 |
3 | C1 | Product C | 30 | 02.02.2019 |
3 | D1 | Product D | 30 | 02.02.2019 |
3 | E1 | Product E | 30 | 02.02.2019 |
4 | C1 | Product C | 31 | 02.01.2019 |
Best wishes
Hi @giulio23
Ok, so what exactly is the expected result for the sample above? Can you show the expected output table, so that the requirement is completely unambiguous?
Please mark the question solved when done and consider giving a thumbs up if posts are helpful.
Contact me privately for support with any larger-scale BI needs, tutoring, etc.
Cheers
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.