Forum Discussion
Place rows in column based on duplicate value
Good day all,
It would help me out big time if someone could help me with the follwing:
Currently my data is stored this way:
| Job | Step | PU At | DO At |
| 1 | 1 | a | b |
| 1 | 2 | b | c |
| 1 | 3 | c | d |
| 2 | 1 | e | f |
| 3 | 1 | g | h |
| 3 | 2 | h | i |
However, I need it the following way:
| Job | Step 1 | PU At 1 | DO At 1 | Step 2 | PU At 2 | DO At 2 | Step 3 | PU At 3 | DO At 3 |
| 1 | 1 | a | b | 2 | b | c | 3 | c | d |
| 2 | 1 | e | f | null | null | null | null | null | null |
| 3 | 1 | g | h | 2 | g | h | null | null | null |
The steps can go up to 10, so that needs to be the maximum amount. Is this possible and if so how can I arrange this. If you need more info please ask.
Thank you in advance!
Anonymous , Paste this code into a blank query in power query and check out the tranformations
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSAeNEIE5SitWBiBiBeTpKyXARYzBPRykFLGIE1ZUKxGlgEWOoSDoQZ8BFjMA8HaVMpdhYAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Job = _t, Step = _t, #"PU At" = _t, #"DO At" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Job", Int64.Type}, {"Step", Int64.Type}, {"PU At", type text}, {"DO At", type text}}), #"Duplicated Column" = Table.DuplicateColumn(#"Changed Type", "Step", "Step - Copy"), #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Duplicated Column", {"Job", "Step - Copy"}, "Attribute", "Value"), #"Merged Columns" = Table.CombineColumns(Table.TransformColumnTypes(#"Unpivoted Columns", {{"Step - Copy", type text}}, "en-US"),{"Attribute", "Step - Copy"},Combiner.CombineTextByDelimiter(" ", QuoteStyle.None),"Merged"), #"Pivoted Column" = Table.Pivot(#"Merged Columns", List.Distinct(#"Merged Columns"[Merged]), "Merged", "Value", List.Max) in #"Pivoted Column"
2 Replies
Anonymous , Paste this code into a blank query in power query and check out the tranformations
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSAeNEIE5SitWBiBiBeTpKyXARYzBPRykFLGIE1ZUKxGlgEWOoSDoQZ8BFjMA8HaVMpdhYAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Job = _t, Step = _t, #"PU At" = _t, #"DO At" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Job", Int64.Type}, {"Step", Int64.Type}, {"PU At", type text}, {"DO At", type text}}), #"Duplicated Column" = Table.DuplicateColumn(#"Changed Type", "Step", "Step - Copy"), #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Duplicated Column", {"Job", "Step - Copy"}, "Attribute", "Value"), #"Merged Columns" = Table.CombineColumns(Table.TransformColumnTypes(#"Unpivoted Columns", {{"Step - Copy", type text}}, "en-US"),{"Attribute", "Step - Copy"},Combiner.CombineTextByDelimiter(" ", QuoteStyle.None),"Merged"), #"Pivoted Column" = Table.Pivot(#"Merged Columns", List.Distinct(#"Merged Columns"[Merged]), "Merged", "Value", List.Max) in #"Pivoted Column"