Multiple value Column to distinct Column Headers with Values from neighborhood Column

[Anonymous]

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!

[v-joesh-msft]

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.

[Anonymous]

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!

[v-joesh-msft · Accepted solution]

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:

https://qiuyunus-my.sharepoint.com/:u:/g/personal/pbipro_qiuyunus_onmicrosoft_com/ESRlj47hu5pEgJjjnX8GZ1oBCzGq_iesHbzKXW_TbvnyOA?e=6gz4IZ

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.

[Anonymous]

Can You please help me one more time.

This script works fine, but sometimes I don't have values for some headers, and so in the end my columns are shifted.

Maybe You know how to fix it?

Many thanks in advance!

[v-joesh-msft]

Hi @Anonymous ,

Try the following code, note the steps InsertAndZip:

let    
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSlTSUTJUitWBsIzArCTcrFgA", 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.Transform(List.Zip(Table.AddColumn(#"Grouped Rows", "Custom", each List.InsertRange(_[Count],0,{[name]}))[Custom]), each List.Transform(_,each if _=null then "" else _)),
    #"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:

https://qiuyunus-my.sharepoint.com/:u:/g/personal/pbipro_qiuyunus_onmicrosoft_com/ESl2J8gOokVFut71A3OnUEAB1K1hL6MAa6SGXrJEjO-96w?e=84p0Dx

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.

[Anonymous]

It is exactly what I needed.

Thank you very much!

[parry2k]

@Anonymous use matrix visual, put  text value column on column section for matrix and numeric value column on value section of matix