Forum Discussion
Non-continuous Complicated Data Structure
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 |
| .... | .... | .... |
That's some nasty source data formatting, invoking ImkeF...
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"
5 Replies
When you say you have a report, are you saying that is how your source data looks?
That's some nasty source data formatting, invoking ImkeF...