Forum Discussion
Solved
Transpose, distinct values based on filter?
I cannot figure out the solution in edit queries. Basically i want to model the table as seen to be a single distinct item for 'item code' and the keywords to take up multiple collumns against th...
Try this code in Query Editor/Power Query
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjRQ0gERsTpQtk9+cmJJZn4eQsQRwXTOzy3ISS1JhYroGhiCdYNoZJGM/GJUJcn5eXmpySX5RSiiiUWpiUqxsQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Item Code" = _t, #"Key Word" = _t]), #"Grouped Rows" = Table.Group(Source, {"Item Code"}, {{"groupRows", each _, type table [Item Code=text, Key Word=text]}}), #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each [groupRows][Key Word]), #"Extracted Values" = Table.TransformColumns(#"Added Custom", {"Custom", each Text.Combine(List.Transform(_, Text.From), ","), type text}), #"Split Column by Delimiter" = Table.SplitColumn(#"Extracted Values", "Custom", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"Custom.1", "Custom.2", "Custom.3", "Custom.4"}) in #"Split Column by Delimiter"Best Regards,
Mariusz
If this post helps, then please consider Accepting it as the solution.
Please feel free to connect with me.
LinkedIn
Try this code in Query Editor/Power Query
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjRQ0gERsTpQtk9+cmJJZn4eQsQRwXTOzy3ISS1JhYroGhiCdYNoZJGM/GJUJcn5eXmpySX5RSiiiUWpiUqxsQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Item Code" = _t, #"Key Word" = _t]),
#"Grouped Rows" = Table.Group(Source, {"Item Code"}, {{"groupRows", each _, type table [Item Code=text, Key Word=text]}}),
#"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each [groupRows][Key Word]),
#"Extracted Values" = Table.TransformColumns(#"Added Custom", {"Custom", each Text.Combine(List.Transform(_, Text.From), ","), type text}),
#"Split Column by Delimiter" = Table.SplitColumn(#"Extracted Values", "Custom", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"Custom.1", "Custom.2", "Custom.3", "Custom.4"})
in
#"Split Column by Delimiter"
Best Regards,
Mariusz
If this post helps, then please consider Accepting it as the solution.
Please feel free to connect with me.
LinkedIn
Mariusz
If this post helps, then please consider Accepting it as the solution.
Please feel free to connect with me.