Forum Discussion

mds123's avatar
mds123
Frequent Visitor
2 years ago
Solved

Splitting semi colon separated columns into rows

Hello, My data source is a list of work orders. Two of my columns in my dataset have semi colons to separate the item codes and quantities that are in the same work order. My goal is to break this i...
Need Help
  • lbendlin's avatar
    lbendlin
    2 years ago
    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMtQ31DcyMDJR0lFydgQShmDCyNjEWsHQCMS0NlKK1UFR5xcJEncCEkamQMLYFKrACKbAzQck54wwyNRawRLIM7U2sjZWio0FAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, Site = _t, #"Work Order" = _t, #"Item Codes" = _t, Quantities = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Site", type text}, {"Work Order", type text}, {"Item Codes", type text}, {"Quantities", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each List.Zip({Text.Split([Item Codes],";"),Text.Split([Quantities],";")})),
    #"Removed Other Columns" = Table.SelectColumns(#"Added Custom",{"Date", "Site", "Work Order", "Custom"}),
    #"Expanded Custom" = Table.ExpandListColumn(#"Removed Other Columns", "Custom"),
    #"Extracted Values" = Table.TransformColumns(#"Expanded Custom", {"Custom", each Text.Combine(List.Transform(_, Text.From), ";"), type text}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Extracted Values", "Custom", Splitter.SplitTextByEachDelimiter({";"}, QuoteStyle.Csv, false), {"Item Code", "Quantity"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Item Code", Int64.Type}, {"Quantity", Int64.Type}})
in
    #"Changed Type1"

    How to use this code: Create a new Blank Query. Click on "Advanced Editor". Replace the code in the window with the code provided here. Click "Done". Once you examined the code, replace the Source step with your own source.

lbendlin's avatar
lbendlin
Super User
2 years ago
let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMtQ31DcyMDJR0lFydgQShmDCyNjEWsHQCMS0NlKK1UFR5xcJEncCEkamQMLYFKrACKbAzQck54wwyNRawRLIM7U2sjZWio0FAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, Site = _t, #"Work Order" = _t, #"Item Codes" = _t, Quantities = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Site", type text}, {"Work Order", type text}, {"Item Codes", type text}, {"Quantities", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each List.Zip({Text.Split([Item Codes],";"),Text.Split([Quantities],";")})),
    #"Removed Other Columns" = Table.SelectColumns(#"Added Custom",{"Date", "Site", "Work Order", "Custom"}),
    #"Expanded Custom" = Table.ExpandListColumn(#"Removed Other Columns", "Custom"),
    #"Extracted Values" = Table.TransformColumns(#"Expanded Custom", {"Custom", each Text.Combine(List.Transform(_, Text.From), ";"), type text}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Extracted Values", "Custom", Splitter.SplitTextByEachDelimiter({";"}, QuoteStyle.Csv, false), {"Item Code", "Quantity"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Item Code", Int64.Type}, {"Quantity", Int64.Type}})
in
    #"Changed Type1"

How to use this code: Create a new Blank Query. Click on "Advanced Editor". Replace the code in the window with the code provided here. Click "Done". Once you examined the code, replace the Source step with your own source.