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
ruben_4HP
Frequent Visitor

Name column with multiple value columns

Dear Power BI community,

 

I'm facing an issue with the following data input:

 

Column1Column2Column3Column4Column5Column6Column7

Column8

Person ADate A#HoursDate XX#HoursDate AA#Hours...
Person BDate B#HoursDate XY#HoursDate AB#Hours...
Person CDate C#HoursDate XZ#HoursDate AC#Hours...
........................

 

With the hours of Column 3 corresponding to the dates in Column2, etc

 

Now my ideal output would be the following:

 

NameDateHours
Person ADate A#Hours
Person ADate B#Hours
......#Hours
Person ADate AC#Hours
Person BDate A#Hours
Person BDate B#Hours
......#Hours

 

Can somebody please help me out? Thank you very much in advance!

 

Best regards,

Ruben.

 

1 ACCEPTED SOLUTION

Hi @ruben_4HP  

please paste the code into the advanced editor and follow the steps:

 

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCkgtKs7PU3BU0lFySSxJBTOUPfJLi4phIhERGEKOSKpideCGOMHknTANicQ0xAmrIc4weWdMQ6IwDUFSFRsLAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t, Column4 = _t, Column5 = _t, Column6 = _t, Column7 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}, {"Column5", type text}, {"Column6", type text}, {"Column7", type text}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Column1"}, "Attribute", "Value"),
    #"Removed Columns" = Table.RemoveColumns(#"Unpivoted Other Columns",{"Attribute"}),
    #"Added Index" = Table.AddIndexColumn(#"Removed Columns", "Index", 0, 1),
    #"Inserted Integer-Division" = Table.AddColumn(#"Added Index", "Integer-Division", each Number.IntegerDivide([Index], 2), Int64.Type),
    #"Calculated Modulo" = Table.TransformColumns(#"Inserted Integer-Division", {{"Index", each Number.Mod(_, 2), type number}}),
    #"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Calculated Modulo", {{"Index", type text}}, "en-GB"), List.Distinct(Table.TransformColumnTypes(#"Calculated Modulo", {{"Index", type text}}, "en-GB")[Index]), "Index", "Value")
in
    #"Pivoted Column"

 

 

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

View solution in original post

4 REPLIES 4
Greg_Deckler
Super User
Super User

@ImkeF

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

Hi @ruben_4HP  

please paste the code into the advanced editor and follow the steps:

 

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCkgtKs7PU3BU0lFySSxJBTOUPfJLi4phIhERGEKOSKpideCGOMHknTANicQ0xAmrIc4weWdMQ6IwDUFSFRsLAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t, Column4 = _t, Column5 = _t, Column6 = _t, Column7 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}, {"Column5", type text}, {"Column6", type text}, {"Column7", type text}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Column1"}, "Attribute", "Value"),
    #"Removed Columns" = Table.RemoveColumns(#"Unpivoted Other Columns",{"Attribute"}),
    #"Added Index" = Table.AddIndexColumn(#"Removed Columns", "Index", 0, 1),
    #"Inserted Integer-Division" = Table.AddColumn(#"Added Index", "Integer-Division", each Number.IntegerDivide([Index], 2), Int64.Type),
    #"Calculated Modulo" = Table.TransformColumns(#"Inserted Integer-Division", {{"Index", each Number.Mod(_, 2), type number}}),
    #"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Calculated Modulo", {{"Index", type text}}, "en-GB"), List.Distinct(Table.TransformColumnTypes(#"Calculated Modulo", {{"Index", type text}}, "en-GB")[Index]), "Index", "Value")
in
    #"Pivoted Column"

 

 

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

Great @ImkeF , works like a charm!

blopez11
Resident Rockstar
Resident Rockstar

In the query editor,  unpivot your date/hour column  for example columns 2 - 7 in your example, and rename resulting as appropriate

I am doing this right now for a data set I have

 

Good luck

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