Transpose, distinct values based on filter?

[johnnyboy_175]

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

[Mariusz · Accepted solution]

HI @johnnyboy_175 

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