Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Dear Power BI community,
I'm facing an issue with the following data input:
Column1 | Column2 | Column3 | Column4 | Column5 | Column6 | Column7 | Column8 |
Person A | Date A | #Hours | Date XX | #Hours | Date AA | #Hours | ... |
Person B | Date B | #Hours | Date XY | #Hours | Date AB | #Hours | ... |
Person C | Date C | #Hours | Date XZ | #Hours | Date AC | #Hours | ... |
... | ... | ... | ... | ... | ... | ... | ... |
With the hours of Column 3 corresponding to the dates in Column2, etc
Now my ideal output would be the following:
Name | Date | Hours |
Person A | Date A | #Hours |
Person A | Date B | #Hours |
... | ... | #Hours |
Person A | Date AC | #Hours |
Person B | Date A | #Hours |
Person B | Date B | #Hours |
... | ... | #Hours |
Can somebody please help me out? Thank you very much in advance!
Best regards,
Ruben.
Solved! Go to 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
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
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