Forum Discussion

Navaneetharaju_'s avatar
Navaneetharaju_
Helper II
2 years ago

Need Suggestion to create calculated column using Power Query Step or column

Hi All, 

 

I need a legend to categorize the components that we used in my table

 

ID          Components

1001      A,B,C

1002     blank

1003     A, C

 

I need a power query step or colunm to split the components and make that split value as legend 

 

Data should be like this - 

 

If i set the components value as legend it should show the components count

 

ID       Components

1001    A

1001    B

1001    C

1002    blank

1003   A

1003  C

 

Legend

 

  • blank
  • A 
  • B
  • C

Expected Result - 

Thanks,

Need Help
Show and Tell
Tips and Tricks

2 Replies

  • JoeBarry's avatar
    JoeBarry
    Solution Sage
    2 years ago

    Hi Navaneetharaju_ 

     

    In Power query, go to Transform. Split Column by delimiter and choose comma. Highlight all columns except id and right click unpiviot columns.

     

    Thanks

    Joe

  • Idrissshatila's avatar
    Idrissshatila
    Super User
    2 years ago

    Hello Navaneetharaju_ ,

     

    You could use the following M code in Power query to reach your desired outcome.

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQwMFTSUXLUcdJxVorVAQsYAQWcchLzsmECxmAVCkAFsQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, Components = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"Components", type text}}),
    #"Inserted Text After Delimiter" = Table.AddColumn(#"Changed Type", "Text After Delimiter", each Text.AfterDelimiter([Components], ","), type text),
    #"Inserted Text After Delimiter1" = Table.AddColumn(#"Inserted Text After Delimiter", "Text After Delimiter.1", each Text.AfterDelimiter([Text After Delimiter], ","), type text),
    #"Extracted Text Before Delimiter" = Table.TransformColumns(#"Inserted Text After Delimiter1", {{"Components", each Text.BeforeDelimiter(_, ","), type text}}),
    #"Extracted Text Before Delimiter1" = Table.TransformColumns(#"Extracted Text Before Delimiter", {{"Text After Delimiter", each Text.BeforeDelimiter(_, ","), type text}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Extracted Text Before Delimiter1", {"ID"}, "Attribute", "Value"),
    #"Replaced Value" = Table.ReplaceValue(#"Unpivoted Other Columns","Text After Delimiter.1","Components",Replacer.ReplaceText,{"Attribute"}),
    #"Replaced Value1" = Table.ReplaceValue(#"Replaced Value","Text After Delimiter","Components",Replacer.ReplaceText,{"Attribute"}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Replaced Value1", {"ID", "Value"}, "Attribute.1", "Value.1"),
    #"Removed Other Columns" = Table.SelectColumns(#"Unpivoted Columns",{"ID", "Value"}),
    #"Filtered Rows" = Table.SelectRows(#"Removed Other Columns", each ([Value] <> ""))
in
    #"Filtered Rows"

     

     

     

    If I answered your question, please mark my post as solution, Appreciate your Kudos đź‘Ť

    Follow me on Linkedin
    Vote For my Ideađź’ˇ