Rows to columns

[DNLAL]

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

[DNLAL · Accepted solution]

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

[DNLAL]

Sorry not quite sure what you mean?  where do I put that

[jennratten]

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"

[Greg_Deckler]

@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"