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 that distinct row.
ie: 10 would have columns containing 10, Location, A, Complete'
ie:10-01 would have columns 10-01, hose, connector,area.
and so on.
Please note that the first keyword is always the distinct value in the key words column.
Cheers JC
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
1 Reply
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