Forum Discussion

Navaneetharaju_'s avatar
Navaneetharaju_
Helper II
3 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 t...
Need Help
Show and Tell
Tips and Tricks
Idrissshatila's avatar
Idrissshatila
Super User
3 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💡