Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
DNLAL
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

 

TIA

1 ACCEPTED SOLUTION
DNLAL
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

4 REPLIES 4
DNLAL
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

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

jennratten_0-1679415093612.png

 

Greg_Deckler
Super User
Super User

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

@ 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

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors