Showing results for 
Search instead for 
Did you mean: 

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

Regular Visitor

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)


ItemDate (it's a date column)CodeValue
Item X   15 MarchI2
Item X15 March Z4
Item X15 March A3
Item Y15 MarchI1
Item Y15 MarchZ6
Item Z15 MarchI2
Item X16 MarchZ2
Item X16 MarchA9


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


ItemDateCode ICode ZCode A
Item X15 March243
Item Y15 March160
Item Z15 March200
Item X16 March029



Regular Visitor

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

View solution in original post

Regular Visitor

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

Regular Visitor

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"})
    #"Replaced Value1"



Super User
Super User

@DNLAL Try this:

    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"})
    #"Replaced Value"

@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

PBI November 2023 Update Carousel

Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

Power BI Fabric Summit Carousel

The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors