Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Navaneetharaju_
Helper II
Helper II

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
  • B
  • C

Expected Result - 

Navaneetharaju__0-1692681182378.png

Thanks,

2 REPLIES 2
Idrissshatila
Super User
Super User

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"

 

Idrissshatila_0-1692684920651.png

 

 

If I answered your question, please mark my post as solution, Appreciate your Kudos 👍

Follow me on Linkedin
Vote For my Idea💡

 



Did I answer your question? Mark my post as a solution! Appreciate your Kudos
Follow me on LinkedIn linkedIn
Vote for my Community Mobile App Idea

Proud to be a Super User!




JoeBarry
Solution Sage
Solution Sage

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.

JoeBarry_0-1692684678602.png

 

Thanks

Joe

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.