Forum Discussion
Multiple value Column to distinct Column Headers with Values from neighborhood Column
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.
Solution SageHi 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.
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:
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.