Power Query unpivot or anything that can solve the problem

Hi Anonymous ,

Try this example query. It transforms your data into the most efficient reporting structure:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jVVNb9swDP0rRoHdClX8FHVddxy2Yuit6CFr3K1AlhRJNqD/fhTlNG6RDAN8eE+yaOqRj767u8hw5Q/UnC8ug/AVZqgTkTnROSlzYp3cX95d3Gw3z+N2/zJc/1xsf4zD7Wa/WPlrQGhci2LHKlwKJJ6YVYkEHFfKSnzAAhkj7udxtxs+juvx8Wm/a7uAyjlDQMqYGftqqTXycliZSDu0iqYR6Mu4Hz6N35/2bQNBTIpCxyRYsPakEDhXsWlHSbHahNmXe6ybxcuvcd3zITb0lByiZA/ExSEpmR9sJ7mYVS0tNTECK8gRY7FcDrfbxXr3OG5boOq3ZghQME5WFKjcV8Bw2uJ+fNV0+TY+/l4v4zQIS0tHKOeWAaKLHqkglIhCzCRyPPz1z7h9Pl6EAUxblqzZQl+2SlE38XykZSRs5kFfQzwvnpbDfjM8bHYRo5WycBdPtWh8XqwtU61T5msvxBsBazaoEAKSWOE4plKQSgkBK7UyTAKiTZ3xYbjerFbjw35cRq0Tk9XqDdYi5dRER79CC5YTIPmHgCKTxLkQVenlSX5LF2+S5o0vIF/5MzV8IzAn+Epo/hq/kn/7grKRZU44seJ3p2QHBlx58sk5dsYfBKKS4naNZTFLZWKuqWEiPDAvTyJ5x07YxcS3LenE2FtCDtgNjQfsDqlv8Du7GLAXwiAVCCYe2TUwPTAGTVG090xbzTSRnnGPu9mLzkdc/g+fMJMoeCumHL2vygqpBnZHiYYIp+A5Z6kYM6SYEarZ/ZkEYp28yVPMF8cqng8GZi0lZT1vtJprqQlaCXxuemHKOcQ+CBKeMp5B5jaQuiDWBqtrnGJ+NeazuvbeCObDL2kvGmafqt64fMKHqn450qzRB4156UTCeY2Vks36NRvziwp2YVwgdzoInvBhkIPB5j8rmP+sYP6zguPP6v7+Lw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Financial Metrics" = _t, Column2 = _t, Column3 = _t, Column4 = _t, Column5 = _t, Column6 = _t]),

// Relevant steps ---->
    transposeTable = Table.Transpose(Source),
    promHeaders = Table.PromoteHeaders(transposeTable, [PromoteAllScalars=true]),
    unpivOthCols = Table.UnpivotOtherColumns(promHeaders, {"01/01/1900"}, "ValueType", "Value"),
// <---- Relevant steps

    renDateCol = Table.RenameColumns(unpivOthCols,{{"01/01/1900", "Date"}})
in
    renDateCol

Example output:

You can obviously clean up the "_x" suffixes etc., but these few steps get you to the correct structure and fix the Date issue.

Pete