Forum Discussion
Multiple value Column to distinct Column Headers with Values from neighborhood Column
Hi, everyone!
As I'm new to Power BI, please, help me to solve my problem.
I have table with two columns. First one with Text Values which duplicates, and second one with Numeric Values.
And I need to transform this table to have unique values of First Column as Headers, and values from Second Column under each Header respectively.
Or if I can simply visualize my data this way from source table.
Many thanks in advance!
Hi Anonymous ,
Is the number of values corresponding to each id the same? If it is the same, you can try the following M code, the "Source" is in my query and starting with "Changed Type", is in yours:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSlTSUTJUitWBsIzQWEnorFgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [name = _t, id = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"name", type text}, {"id", type text}}), #"Grouped Rows" = Table.Group(#"Changed Type", {"name"}, {{"Count", each _[id], type list}}), InsertAndZip = List.Zip(Table.AddColumn(#"Grouped Rows", "Custom", each List.InsertRange(_[Count],0,{[name]}))[Custom]), #"Converted to Table" = Table.FromList(InsertAndZip, Splitter.SplitByNothing(), null, null, ExtraValues.Error), #"Extracted Values" = Table.TransformColumns(#"Converted to Table", {"Column1", each Text.Combine(List.Transform(_, Text.From), ","), type text}), #"Split Column by Delimiter" = Table.SplitColumn(#"Extracted Values", "Column1", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"Column1.1", "Column1.2"}), #"Promoted Headers" = Table.PromoteHeaders(#"Split Column by Delimiter", [PromoteAllScalars=true]), #"Changed Type2" = Table.TransformColumnTypes(#"Promoted Headers",{{"a", Int64.Type}, {"b", Int64.Type}}) in #"Changed Type2"Here is a demo, please try it:
Best Regards,
Community Support Team _ Joey
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
7 Replies
Anonymous use matrix visual, put text value column on column section for matrix and numeric value column on value section of matix
Hi Anonymous ,
As parry2k said, you can use matrix visual, the result is shown below:
Best Regards,
Community Support Team _ Joey
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.Thank you, but it's not that I wanted.
As I have several values for the ID, matrix will summarize or other functions, however it will show one value for each ID, when I need to show ID values as a column, and under each ID all values without any functions.
I found the way with Quick measures to make a string from Values.
ID1 | ID 2 | ...
____________
2 | 5.5 | ...
54 | 44 | ...
... | ... | ...
But still I have a question if there any possibility to convert table to this view in Edit Query mode.
Many thanks in advance!
Hi Anonymous ,
Is the number of values corresponding to each id the same? If it is the same, you can try the following M code, the "Source" is in my query and starting with "Changed Type", is in yours:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSlTSUTJUitWBsIzQWEnorFgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [name = _t, id = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"name", type text}, {"id", type text}}), #"Grouped Rows" = Table.Group(#"Changed Type", {"name"}, {{"Count", each _[id], type list}}), InsertAndZip = List.Zip(Table.AddColumn(#"Grouped Rows", "Custom", each List.InsertRange(_[Count],0,{[name]}))[Custom]), #"Converted to Table" = Table.FromList(InsertAndZip, Splitter.SplitByNothing(), null, null, ExtraValues.Error), #"Extracted Values" = Table.TransformColumns(#"Converted to Table", {"Column1", each Text.Combine(List.Transform(_, Text.From), ","), type text}), #"Split Column by Delimiter" = Table.SplitColumn(#"Extracted Values", "Column1", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"Column1.1", "Column1.2"}), #"Promoted Headers" = Table.PromoteHeaders(#"Split Column by Delimiter", [PromoteAllScalars=true]), #"Changed Type2" = Table.TransformColumnTypes(#"Promoted Headers",{{"a", Int64.Type}, {"b", Int64.Type}}) in #"Changed Type2"Here is a demo, please try it:
Best Regards,
Community Support Team _ Joey
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
