Forum Discussion
Solved
Data shaping cleaning
I have the kind excel file with double header and need help transforming structured table with single header. Here excel Data below Column1 Column2 Column3 Column4 Column5 Column6 ...
You could try something like this...
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUXICYmcgdgFiV6VYnWil4Pzc1JKMzLx0oAgCgWQMgQwjIDYGYhMgNlWKjQUA", 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]),
headers = Table.FirstN(Source,1),
remove = Table.Skip(Source,2),
combine = Table.Combine({headers, remove}),
promote = Table.PromoteHeaders(combine)
in
promote
Paste it into the advanced editor of a blank query to see what it's doing.
| Column1 | Column2 | Column3 | Column4 | Column5 | Column6 | Column7 | Column8 | Column9 | Column10 |
| House plan System | |||||||||
| Goal 1 | Goal 2 | ||||||||
| Metrics | Actual | Target | Metric # | Total # | Metrics | Actual | Target | Metric # | Total # |
| % of Objectives met. | 0% | TBD | 0 | 3 | % of Objectives met. | 0% | TBD | 0 | 4 |
| % of Objectives me by original date. | 0% | TBD | 0 | 3 | % of Objectives me by original date. | 0% | TBD | 0 | 4 |
| % of Objectives me by adjusted date. | 0% | TBD | 0 | 3 | % of Objectives me by adjusted date. | 0% | TBD | 0 | 4 |
| % of Action Step met. | 6% | TBD | 1 | 16 | % of Action Step met. | 38% | TBD | 6 | 16 |
| % of Action Step met by original date | 6% | TBD | 1 | 16 | % of Action Step met by original date | 31% | TBD | 5 | 16 |
| % of Action Step met by adjusted date | 0% | TBD | 0 | 16 | % of Action Step met by adjusted date | 6% | TBD | 1 | 16 |
| % of Action Steps revised. | 25% | TBD | 4 | 16 | % of Action Steps revised. | 6% | TBD | 1 | 16 |
| Goal 3 | Goal 4 | ||||||||
| Metrics | Actual | Target | Metric # | Total # | Metrics | Actual | Target | Metric # | Total # |
| % of Objectives met. | 0% | TBD | 0 | 3 | % of Objectives met. | 0% | TBD | 0 | 4 |
| % of Objectives me by original date. | 0% | TBD | 0 | 3 | % of Objectives me by original date. | 0% | TBD | 0 | 4 |
| % of Objectives me by adjusted date. | 0% | TBD | 0 | 3 | % of Objectives me by adjusted date. | 0% | TBD | 0 | 4 |
| % of Action Step met. | 6% | TBD | 1 | 16 | % of Action Step met. | 38% | TBD | 6 | 16 |
| % of Action Step met by original date | 6% | TBD | 1 | 16 | % of Action Step met by original date | 31% | TBD | 5 | 16 |
| % of Action Step met by adjusted date | 0% | TBD | 0 | 16 | % of Action Step met by adjusted date | 6% | TBD | 1 | 16 |
| % of Action Steps revised. | 25% | TBD | 4 | 16 | % of Action Steps revised. | 6% | TBD | 1 | 16 |
| Goal 5 | House plan System totals | ||||||||
| Metrics | Actual | Target | Metric # | Total # | Metrics | Actual | Target | Metric # | Total # |
| % of Objectives met. | 0% | TBD | 0 | 3 | % of Objectives met. | 0% | TBD | 0 | 4 |
| % of Objectives me by original date. | 0% | TBD | 0 | 3 | % of Objectives me by original date. | 0% | TBD | 0 | 4 |
| % of Objectives me by adjusted date. | 0% | TBD | 0 | 3 | % of Objectives me by adjusted date. | 0% | TBD | 0 | 4 |
| % of Action Step met. | 6% | TBD | 1 | 16 | % of Action Step met. | 38% | TBD | 6 | 16 |
| % of Action Step met by original date | 6% | TBD | 1 | 16 | % of Action Step met by original date | 31% | TBD | 5 | 16 |
| % of Action Step met by adjusted date | 0% | TBD | 0 | 16 | % of Action Step met by adjusted date | 6% | TBD | 1 | 16 |
| % of Action Steps revised. | 25% | TBD | 4 | 16 | % of Action Steps revised. | 6% | TBD | 1 | 16 |