CODEWITHSUNDEEP
powerbipowerbi-desktoppowerbi-datasourcepowerbi-custom-visualspower-bi-report-server
Sai Teja Bandela
Sai Teja Bandela

Reputation: 1

How to match the corresponding values of Category and Amount columns?

enter image description here

I'm new to power bi.I tried split delimiter by | and it was creating multiple rows with each category matching with each amount value of the same order id. But, I want only corresponding values of the category and amount to be matched.

Upvotes: 0

Views: 43

Answers (1)

Ryan
Ryan

Reputation: 2480

not sure if we have an easier way to solve this. Here is a workaround for you as a reference.

  1. Split the Category column by "|" enter image description here

  2. do the same for column Amount enter image description here

  3. select order id column and unpivot other columns enter image description here enter image description here

  4. for the attribute column , only remain the number enter image description here

  5. use group by function to combine the text and number which the attribute is the same enter image description here

  6. remove the column attribute and split the combined column enter image description here

  7. rename the column name enter image description here

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bVC7bsMwDPwVQ3NCiLQe0egEyJoA7RZ4cF0lNmDIhmQPAfrxpWoPCdCF4h3vjgJvN3Gq9iQR92gsOid24tiHbx9T8VNUceZ67cbgMzw3afZhHV2byUcWG+nAHZjQoPJTOlyx1RqcyQwCkqh3L5uUkyTZfLnf+9YXH8s0Df26Ygn9vESfeEpbMAeqd3+JTup//Z++7cI4jI/nG2CtBWJKEUg8mK0r9WsukjWoWFq1rU9pjFtk8zX8Ndtd8s9KBUozhaQNUA5GC8qIuv4F", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Order ID" = _t, Category = _t, Amount = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Order ID", type text}, {"Category", type text}, {"Amount", type text}}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Changed Type", "Category", Splitter.SplitTextByDelimiter("|", QuoteStyle.Csv), {"Category.1", "Category.2", "Category.3", "Category.4", "Category.5"}),
    #"Split Column by Delimiter1" = Table.SplitColumn(#"Split Column by Delimiter", "Amount", Splitter.SplitTextByDelimiter("|", QuoteStyle.Csv), {"Amount.1", "Amount.2", "Amount.3", "Amount.4", "Amount.5"}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Split Column by Delimiter1", {"Order ID"}, "Attribute", "Value"),
    #"Extracted Last Characters" = Table.TransformColumns(#"Unpivoted Other Columns", {{"Attribute", each Text.End(_, 1), type text}}),
    #"Grouped Rows" = Table.Group(#"Extracted Last Characters", {"Order ID", "Attribute"}, {{"text", each Text.Combine([Value],","), type any}}),
    #"Removed Columns" = Table.RemoveColumns(#"Grouped Rows",{"Attribute"}),
    #"Split Column by Delimiter2" = Table.SplitColumn(#"Removed Columns", "text", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"text.1", "text.2"}),
    #"Changed Type4" = Table.TransformColumnTypes(#"Split Column by Delimiter2",{{"text.1", type text}, {"text.2", type number}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type4",{{"text.1", "Category"}, {"text.2", "Amount"}})
in
    #"Renamed Columns"

Upvotes: 0

Related Questions