Forum Discussion
Transform merged tables with labels in data column
I have the following Table 1, which I receive in this format for multiple files, and would like to transform it into a more structured form (Table 2 below). Can anyone offer guidance on how to do so?...
See the working here - Open a blank query - Home - Advanced Editor - Remove everything from there and paste the below code to test
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCk4tUXBU0lECoVidaKWA1JLUIiDHHIgN9AxNwYJeiXmpQL4Zilh+Rh6QbwwWMwILOeUnAXmGYBFjsEhIfklijgLMDkOIoQYQA0CiTkg2+yYWVQLZRhADIWrCM3NyMhNzi9HFvRNLMBzknZqHZntQZnJGYlEKmis98sshgpYQ3WgOBTnJyBju0FgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Frequency = _t, Cost = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Frequency", Int64.Type}, {"Cost", type number}}), #"Added Custom" = Table.AddColumn(#"Changed Type", "Set", each if [Frequency]=null and [Cost]=null then [Column1] else null), #"Filled Down" = Table.FillDown(#"Added Custom",{"Set"}), #"Filtered Rows" = Table.SelectRows(#"Filled Down", each not Text.StartsWith([Column1], "Set") and not Text.StartsWith([Column1], "Total")) in #"Filtered Rows"
Vijay_A_Verma
Most Valuable Professional
Most Valuable ProfessionalSee the working here - Open a blank query - Home - Advanced Editor - Remove everything from there and paste the below code to test
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCk4tUXBU0lECoVidaKWA1JLUIiDHHIgN9AxNwYJeiXmpQL4Zilh+Rh6QbwwWMwILOeUnAXmGYBFjsEhIfklijgLMDkOIoQYQA0CiTkg2+yYWVQLZRhADIWrCM3NyMhNzi9HFvRNLMBzknZqHZntQZnJGYlEKmis98sshgpYQ3WgOBTnJyBju0FgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Frequency = _t, Cost = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Frequency", Int64.Type}, {"Cost", type number}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Set", each if [Frequency]=null and [Cost]=null then [Column1] else null),
#"Filled Down" = Table.FillDown(#"Added Custom",{"Set"}),
#"Filtered Rows" = Table.SelectRows(#"Filled Down", each not Text.StartsWith([Column1], "Set") and not Text.StartsWith([Column1], "Total"))
in
#"Filtered Rows"