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