Forum Discussion
Rows to columns
I know this is a common one ! and lots of questions/videos etc.. however dumb as I am I can't quite hit on the exact steps to do what I need ! I have been unpivoting and pivoting etc.. but haven't managed to get it in the right combination. Am hoping someone can help 🙂
Here's what I have (table looks a bit funny but there are currently 4 columns)
| Item | Date (it's a date column) | Code | Value |
| Item X | 15 March | I | 2 |
| Item X | 15 March | Z | 4 |
| Item X | 15 March | A | 3 |
| Item Y | 15 March | I | 1 |
| Item Y | 15 March | Z | 6 |
| Item Z | 15 March | I | 2 |
| Item X | 16 March | Z | 2 |
| Item X | 16 March | A | 9 |
So each Item could have multiple rows for the same date... Here's what I want to see only one row per item per date
| Item | Date | Code I | Code Z | Code A |
| Item X | 15 March | 2 | 4 | 3 |
| Item Y | 15 March | 1 | 6 | 0 |
| Item Z | 15 March | 2 | 0 | 0 |
| Item X | 16 March | 0 | 2 | 9 |
TIA
So in the end I just pivoted on the code column using the value column for the value and that gave me what I needed. Thanks for your help anyway
4 Replies
DNLAL Try this:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8ixJzVWIUNJRMjRV8E0sSs4AMj2B2EgpVgeLrAKQHQXEJrilHYHYGCEdiWm2IU5ZkNFmCNkowu4yQ9GLWxbkKkul2FgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Item = _t, Date = _t, Code = _t, Value = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Item", type text}, {"Date", type date}, {"Code", type text}, {"Value", Int64.Type}}), #"Pivoted Column" = Table.Pivot(#"Changed Type", List.Distinct(#"Changed Type"[Code]), "Code", "Value", List.Sum), #"Replaced Value" = Table.ReplaceValue(#"Pivoted Column",null,0,Replacer.ReplaceValue,{"I", "Z", "A"}) in #"Replaced Value"Sorry not quite sure what you mean? where do I put that
Select your table in Power Query, click Advanced Editor on the ribbon, copy the script below, add a comma to the last line before the 'in' at the botton, remove the 'in' and the last line, paste the snip at the end. In the Replaced Value step, replace #"Changed Type" with the value that appears before the equals sign in the line above it.
#"Replaced Value" = Table.ReplaceValue(#"Changed Type", each [Code], each "Code " & [Code],Replacer.ReplaceText,{"Code"}), #"Pivoted Column1" = Table.Pivot(#"Replaced Value", List.Distinct(#"Replaced Value"[Code]), "Code", "Value"), #"Replaced Value1" = Table.ReplaceValue(#"Pivoted Column1",null,0,Replacer.ReplaceValue,{"Code I", "Code Z", "Code A"}) in #"Replaced Value1"
So in the end I just pivoted on the code column using the value column for the value and that gave me what I needed. Thanks for your help anyway
