Forum Discussion
Transform time series data date in header to rows after extracting using power query
Hi
I extract data from web link where it has first row as variables date in days. It works fine if I create pivot table to analyse data by day. However, I would like to plot time series, may I know what is the best way to transform the date on headers to rows? I tried power query "transform data", but it is not working. Please refer to example below
- Select your Group column and then Unpivot other columns.
Anonymous ,
Try this code on Transform Data -> Home -> Advanced Editor:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTKEYiMgNgHjWJ1oJSeoiCkStgDLOANZxnC1OkrmYBwbCwA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Group = _t, #"1/22/20" = _t, #"1/23/20" = _t, #"1/24/20" = _t, #"1/25/20" = _t, #"1/26/20" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Group", type text}, {"1/22/20", Int64.Type}, {"1/23/20", Int64.Type}, {"1/24/20", Int64.Type}, {"1/25/20", Int64.Type}, {"1/26/20", Int64.Type}}),
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Group"}, "Attribute", "Value"),
#"Changed Type with Locale" = Table.TransformColumnTypes(#"Unpivoted Other Columns", {{"Attribute", type date}}, "en-US")
in
#"Changed Type with Locale"Ricardo
2 Replies
- Greg_Deckler
Community Champion
Select your Group column and then Unpivot other columns. 
camargos88Anonymous ,
Try this code on Transform Data -> Home -> Advanced Editor:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTKEYiMgNgHjWJ1oJSeoiCkStgDLOANZxnC1OkrmYBwbCwA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Group = _t, #"1/22/20" = _t, #"1/23/20" = _t, #"1/24/20" = _t, #"1/25/20" = _t, #"1/26/20" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Group", type text}, {"1/22/20", Int64.Type}, {"1/23/20", Int64.Type}, {"1/24/20", Int64.Type}, {"1/25/20", Int64.Type}, {"1/26/20", Int64.Type}}),
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Group"}, "Attribute", "Value"),
#"Changed Type with Locale" = Table.TransformColumnTypes(#"Unpivoted Other Columns", {{"Attribute", type date}}, "en-US")
in
#"Changed Type with Locale"Ricardo