Forum Discussion
Solved
Transforming headers and data that is across multiple rows
I'm struggling to tranform a table that was scraped from a website. The headers and data run across two rows so need to shift row 3 to the end of row 1 and row 4 to the end of row 2. I thought of cre...
simonmcd
Hi , this shoudl work for you. past eht ecode on a new blank query and check the steps.let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCijKTE5V0oHQCs4ZiXnpqQqqQAGnzBQg6Z+WlloEogtS85RidaKVDIEcIyA2BmITIDYFi/omFmWnlgC5wRmJRanFIO2pJYlAyrUAxHEBkiBlZkC2ORBbALElEBsaKMXGAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t, Column4 = _t, Column5 = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}, {"Column5", type text}}), #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 1, 1, Int64.Type), #"Calculated Modulo" = Table.TransformColumns(#"Added Index", {{"Index", each Number.Mod(_, 2), type number}}), Custom1 = #"Calculated Modulo", #"Kept Last Rows" = Table.PromoteHeaders(Table.LastN(Custom1, 2)), #"Kept First Rows" = Table.PromoteHeaders(Table.FirstN(Custom1,2)), #"Merged Queries" = Table.NestedJoin(#"Kept First Rows", {"1"}, #"Kept Last Rows", {"1"}, "Kept First Rows", JoinKind.LeftOuter), #"Expanded Kept First Rows" = Table.ExpandTableColumn(#"Merged Queries", "Kept First Rows", {"Market", "Shares", "Beta", "Eps", "Dps"}, {"Market", "Shares", "Beta", "Eps", "Dps"}), #"Removed Columns" = Table.RemoveColumns(#"Expanded Kept First Rows",{"1"}) in #"Removed Columns"
Data:
Result:
Thanks for the reply. There are only 4 rows of data, the table is fixed and won't expand from what you can see on the screen shot. Only the data inside the table will change over time.
Fowmy
Super User
Super Usersimonmcd
Hi , this shoudl work for you. past eht ecode on a new blank query and check the steps.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCijKTE5V0oHQCs4ZiXnpqQqqQAGnzBQg6Z+WlloEogtS85RidaKVDIEcIyA2BmITIDYFi/omFmWnlgC5wRmJRanFIO2pJYlAyrUAxHEBkiBlZkC2ORBbALElEBsaKMXGAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t, Column4 = _t, Column5 = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}, {"Column5", type text}}),
#"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 1, 1, Int64.Type),
#"Calculated Modulo" = Table.TransformColumns(#"Added Index", {{"Index", each Number.Mod(_, 2), type number}}),
Custom1 = #"Calculated Modulo",
#"Kept Last Rows" = Table.PromoteHeaders(Table.LastN(Custom1, 2)),
#"Kept First Rows" = Table.PromoteHeaders(Table.FirstN(Custom1,2)),
#"Merged Queries" = Table.NestedJoin(#"Kept First Rows", {"1"}, #"Kept Last Rows", {"1"}, "Kept First Rows", JoinKind.LeftOuter),
#"Expanded Kept First Rows" = Table.ExpandTableColumn(#"Merged Queries", "Kept First Rows", {"Market", "Shares", "Beta", "Eps", "Dps"}, {"Market", "Shares", "Beta", "Eps", "Dps"}),
#"Removed Columns" = Table.RemoveColumns(#"Expanded Kept First Rows",{"1"})
in
#"Removed Columns"
Data:
Result:
That works beautifully! Thanks. I had a funny feeling it would be really complicated : )