Non-continuous Complicated Data Structure

[Anonymous]

hey guys

I have a report like this,

Score 1-7 Jan 2018	Sun	Mon	Tue	Wed	Thu	Fri	Sat
Name	1/1/2018	2/1/2018	3/1/2018	4/1/2018	5/1/2018	6/1/2018	7/1/2018
VK	A	A	B	B	C	C	D
Dingdong	C	D	A	A	C	C	A
Score 8-14 Jan 2018	Sun	Mon	Tue	Wed	Thu	Fri	Sat
Name	8/1/2018	9/1/2018	10/1/2018	11/1/2018	12/1/2018	13/1/2018	14/1/2018
VK	A	A	B	B	C	C	D
Dingdong	D	D	A	A	C	C	A
Score 15-21 Jan 2018	Sun	Mon	Tue	Wed	Thu	Fri	Sat
Name	15/1/2018	16/1/2018	17/1/2018	18/1/2018	19/1/2018	20/1/2018	21/1/2018
VK	A	A	B	B	C	C	D
Dingdong	D	D	A	A	C	C	A

how do I format it to something like this? 

Date	Name	Score
VK	1/1/2018	A
Dingdong	1/1/2018	C
VK	2/1/2018	A
Dingdong	2/1/2018	D
....	....	....

[Greg_Deckler · Accepted solution]

That's some nasty source data formatting, invoking @ImkeF...

[Greg_Deckler]

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

[Anonymous]

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.

[Greg_Deckler · Accepted solution]

That's some nasty source data formatting, invoking @ImkeF...

[ImkeF · Accepted solution]

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"

[Greg_Deckler]

Nifty.