Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago

Value and Identifier in 2 Columns

I'm pretty sure this is rather easy but I cant figure out the best terms to search for the solution.   I have numerical values that I want to sum but their "identifier" is next to the value in anot...
Need Help
CNENFRNL's avatar
CNENFRNL
Community Champion
5 years ago

Hi, Anonymous , maybe you're intended to convert the dataset like this?

If so, try the codes below,

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCkstyg8oLUoNz8gsSVXSUTI107O0NAIyHNMzc0qLjQ2cc1ITi4B8Y2M9M0NLpVgdiB7fxPTUvJJEoIQBEKObYmShZ2ZgCleMYoGRnqGpKTYLTPQMTA2x6jG21DMwwuYoIzM9CxNzrHpMTPTMTM2x6zE0AbotFgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Model Material 1" = _t, #"Model Material 1 Usage" = _t, #"Model Material 2" = _t, #"Model Material 2 Usage" = _t]),

    #"Merged Columns" = Table.CombineColumns(Source,{"Model Material 1", "Model Material 1 Usage"},Combiner.CombineTextByDelimiter(";", QuoteStyle.None),"1"),
    #"Merged Columns1" = Table.CombineColumns(#"Merged Columns",{"Model Material 2", "Model Material 2 Usage"},Combiner.CombineTextByDelimiter(";", QuoteStyle.None),"2"),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Merged Columns1", {}, "Model", "Value"),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Unpivoted Columns", "Value", Splitter.SplitTextByDelimiter(";", QuoteStyle.Csv), {"Material", "Usage"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Material", type text}, {"Usage", type number}})
in
    #"Changed Type"