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? Thanks!
Table 1
| Frequency | Cost | |
| Set A | ||
| Peter | 7 | 0.15 |
| Jane | 6 | 0.15 |
| John | 3 | 0.2 |
| Bob | 1 | 0.3 |
| Total Set A | 17 | 0.05 |
| Set B | ||
| Mary | 2 | 0.25 |
| Williams | 2 | 0.25 |
| Kate | 6 | 0.15 |
| Ken | 1 | 0.3 |
| Richard | 3 | 0.2 |
| Howard | 9 | 0.1 |
| Total Set B | 23 | 0.05 |
Table 2
| Frequency | Cost | Set | |
| Peter | 7 | 0.15 | Set A |
| Jane | 6 | 0.15 | Set A |
| John | 3 | 0.2 | Set A |
| Bob | 1 | 0.3 | Set A |
| Mary | 2 | 0.25 | Set B |
| Williams | 2 | 0.25 | Set B |
| Kate | 6 | 0.15 | Set B |
| Ken | 1 | 0.3 | Set B |
| Richard | 3 | 0.2 | Set B |
| Howard | 9 | 0.1 | Set B |
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"
1 Reply
- Vijay_A_Verma
Most Valuable Professional
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"