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

[Navaneetharaju_]

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,

[Idrissshatila]

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💡

[JoeBarry]

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