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
Anonymous
Not applicable

Non-continuous Complicated Data Structure

hey guys

 

I have a report like this,

 

Score 1-7 Jan 2018SunMonTueWedThuFriSat
Name1/1/20182/1/20183/1/20184/1/20185/1/20186/1/20187/1/2018
VKAABBCCD
DingdongCDAACCA
        
        
Score 8-14 Jan 2018SunMonTueWedThuFriSat
Name8/1/20189/1/201810/1/201811/1/201812/1/201813/1/201814/1/2018
VKAABBCCD
DingdongDDAACCA
        
Score 15-21 Jan 2018SunMonTueWedThuFriSat
Name15/1/201816/1/201817/1/201818/1/201819/1/201820/1/201821/1/2018
VKAABBCCD
DingdongDDAACCA

 

how do I format it to something like this? 

 

DateNameScore
VK1/1/2018A
Dingdong1/1/2018C
VK2/1/2018A
Dingdong2/1/2018D
............
2 ACCEPTED SOLUTIONS

That's some nasty source data formatting, invoking @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...

View solution in original post

That's what they invented Power Query for 🙂

Please paste this code into the advanced editor and follow the steps:

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("tZGxDoIwFEV/hXSGyK0gOKLEQaMLRgfiQIQggyUh8v+2CHgH4yAx6Xt5p31t2tM0FYfsXghbuJjpIV2EBiTDnMFj8BkWDMEIFzsVp52ei/pY9bHuI+5a4kqVea3KcfK9YWiMukZdfBjfV5Jr3RRW6MCztpmy+ksmrdJ5X5t8bI2Ec5Gb+tbqvGkq05M9uhMGSyE/ckkAl4FlgmWCZcKbYCn+0dLLBXxHYqIM8PeDvx8BAzsDO5PsTOI/Mi5P", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Score 1-7 Jan 2018" = _t, Sun = _t, Mon = _t, Tue = _t, Wed = _t, Thu = _t, Fri = _t, Sat = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Score 1-7 Jan 2018", type text}, {"Sun", type text}, {"Mon", type text}, {"Tue", type text}, {"Wed", type text}, {"Thu", type text}, {"Fri", type text}, {"Sat", type text}}),
    #"Demoted Headers" = Table.DemoteHeaders(#"Changed Type"),
    #"Filtered Rows" = Table.SelectRows(#"Demoted Headers", each ([Column1] <> "")),
    #"Added Index" = Table.AddIndexColumn(#"Filtered Rows", "Index", 0, 1),
    #"Inserted Modulo" = Table.AddColumn(#"Added Index", "Modulo", each Number.Mod([Index], 4), type number),
    #"Integer-Divided Column" = Table.TransformColumns(#"Inserted Modulo", {{"Index", each Number.IntegerDivide(_, 4), Int64.Type}}),
    #"Filtered Rows1" = Table.SelectRows(#"Integer-Divided Column", each ([Modulo] <> 0)),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows1",{"Modulo"}),
    #"Grouped Rows" = Table.Group(#"Removed Columns", {"Index"}, {{"Partition", each _, type table}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.PromoteHeaders(Table.FromColumns(Table.ToRows(Table.RemoveColumns([Partition], {"Index"}))))),
    #"Removed Other Columns" = Table.SelectColumns(#"Added Custom",{"Custom"}),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Removed Other Columns", "Custom", {"Name", "VK", "Dingdong"}, {"Name", "VK", "Dingdong"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Expanded Custom",{{"Name", type date}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type1", {"Name"}, "Attribute", "Value")
in
    #"Unpivoted Other Columns"

 

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

5 REPLIES 5
Greg_Deckler
Super User
Super User

When you say you have a report, are you saying that is how your source data looks?


@ 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...
Anonymous
Not applicable

yes, data is populated in such format. Week by week. Previously I used VBA to capture area by area. Was thinking if there’s a better way using Power BI.

That's some nasty source data formatting, invoking @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...

That's what they invented Power Query for 🙂

Please paste this code into the advanced editor and follow the steps:

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("tZGxDoIwFEV/hXSGyK0gOKLEQaMLRgfiQIQggyUh8v+2CHgH4yAx6Xt5p31t2tM0FYfsXghbuJjpIV2EBiTDnMFj8BkWDMEIFzsVp52ei/pY9bHuI+5a4kqVea3KcfK9YWiMukZdfBjfV5Jr3RRW6MCztpmy+ksmrdJ5X5t8bI2Ec5Gb+tbqvGkq05M9uhMGSyE/ckkAl4FlgmWCZcKbYCn+0dLLBXxHYqIM8PeDvx8BAzsDO5PsTOI/Mi5P", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Score 1-7 Jan 2018" = _t, Sun = _t, Mon = _t, Tue = _t, Wed = _t, Thu = _t, Fri = _t, Sat = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Score 1-7 Jan 2018", type text}, {"Sun", type text}, {"Mon", type text}, {"Tue", type text}, {"Wed", type text}, {"Thu", type text}, {"Fri", type text}, {"Sat", type text}}),
    #"Demoted Headers" = Table.DemoteHeaders(#"Changed Type"),
    #"Filtered Rows" = Table.SelectRows(#"Demoted Headers", each ([Column1] <> "")),
    #"Added Index" = Table.AddIndexColumn(#"Filtered Rows", "Index", 0, 1),
    #"Inserted Modulo" = Table.AddColumn(#"Added Index", "Modulo", each Number.Mod([Index], 4), type number),
    #"Integer-Divided Column" = Table.TransformColumns(#"Inserted Modulo", {{"Index", each Number.IntegerDivide(_, 4), Int64.Type}}),
    #"Filtered Rows1" = Table.SelectRows(#"Integer-Divided Column", each ([Modulo] <> 0)),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows1",{"Modulo"}),
    #"Grouped Rows" = Table.Group(#"Removed Columns", {"Index"}, {{"Partition", each _, type table}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.PromoteHeaders(Table.FromColumns(Table.ToRows(Table.RemoveColumns([Partition], {"Index"}))))),
    #"Removed Other Columns" = Table.SelectColumns(#"Added Custom",{"Custom"}),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Removed Other Columns", "Custom", {"Name", "VK", "Dingdong"}, {"Name", "VK", "Dingdong"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Expanded Custom",{{"Name", type date}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type1", {"Name"}, "Attribute", "Value")
in
    #"Unpivoted Other Columns"

 

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

Nifty.


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