Power query related issue

[Anony_mous]

Hello all ,

I am trying to clean and transform the data the data is as follows(this is dirty data)

Order ID.              Category             Amount 
CA-2011-167199   Binders | Art | Phones | Fasteners | Paper      56.7 | 31 | 45 | 52 | 30
CA-2011-149020   office supplies | Furnitures     2.98 | 51
CA-2011-131905   Accessories | Tables | Binders     7.2 | 43.1 | 17.43

I need to convert this dirty data into clean data like this:

Order ID                category       Amount
CA-2011-167199   Binders            56.7
CA-2011-167199    Art                     31
CA-2011-167199    Phones            45
CA-2011-167199     Fasteners       52
CA-2011-167199      Paper             30
CA-2011-149020    office supplies  2.9
CA-2011-149020    Furniture            51
CA-2011-131905    Accessories      7.2
CA-2011-131905     Tables               43.1
CA-2011-131905     Binders           17.43

All suggestions are welcome thank you in advance

[CNENFRNL]

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("VU67DoIwFP2VprM0vX1QO6KJM4MbYUAssQkppIWNj/Fb/DJvxUGXk3tyHvc0DT1XheAABZQGrKUHevLh7mIiG6ni8npupH5MwWV+6dLiwq7V3ewiunXJDFIJCEojaJEpp+3hp1tZLji6p2HwvSNpnefR751rDH5Zo0soC2aPuQL+0xIs1yhXfe9SmuKevHa38XN8B6PBsPxcSZbXgGFK0rZ9Aw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Order ID" = _t, Category = _t, Amount = _t]),

    Transformed = Table.ReplaceValue(Source, each [Category], each [Amount], (x,y,z) => Table.FromColumns(List.Accumulate({y,z}, {}, (s,c) => s & {List.Transform(Text.Split(c, "|"), Text.Trim)})), {"Category"}),
    #"Expanded Category" = Table.ExpandTableColumn(Table.RemoveColumns(Transformed,{"Amount"}), "Category", {"Column1", "Column2"}, {"Category", "Amount"})
in
    #"Expanded Category"

[tackytechtom · Accepted solution]

Hi @Anony_mous ,

How about this:

Before:

After:

Here the code in Power Query M that you can paste into the advanced editor (if you do not know, how to exactly do this, please check out this quick walkthrough)

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Xcs7DoAgFETRrRhqJDwQySvRxBXYERs/iTYUqJ2Ld7QylidzJ0bRhtJoopJqT8xCimZL85L34ipCPmBXKw9YEoP85BVrozF3Z05PPG5pB41iwP1iS6wd1n6ZVszvB/TKQJVVyIcb", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Order ID" = _t, Category = _t, Amount = _t]),
    #"Split Column by Delimiter 1" = Table.SplitColumn(Source, "Category", Splitter.SplitTextByDelimiter(" | ", QuoteStyle.Csv), {"Category", "Category.2"}),
    #"Split Column by Delimiter 2" = Table.SplitColumn(#"Split Column by Delimiter 1", "Amount", Splitter.SplitTextByDelimiter(" | ", QuoteStyle.Csv), {"Amount", "Amount.2"}),
    #"Removed Other Columns 1" = Table.SelectColumns(#"Split Column by Delimiter 2",{"Order ID", "Category.2", "Amount.2"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Other Columns 1",{{"Category.2", "Category"}, {"Amount.2", "Amount"}}),
    #"Removed Other Columns 2" = Table.SelectColumns(#"Split Column by Delimiter 2",{"Order ID", "Category", "Amount"}),
    #"Appended Query" = Table.Combine({#"Removed Other Columns 2", #"Renamed Columns"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Appended Query",{{"Amount", type number}})
in
    #"Changed Type"

Let me know if this works for you 🙂

/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/