Data shaping cleaning

[redomer44]

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	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

[Ashish_Mathur]

Hi,

Paste that data in an MS Excel file and in another tab of that file, show the expected result.

[redomer44]

[KNP]

What is your desired output?

[redomer44 · Accepted solution]

[Anonymous]

Thanks for the replies from Ashish_Mathur and KNP.

Hi @redomer44 ,

Please follow these steps in Power Query Editor:

1.Duplicate table.

2.Delete the unwanted rows for both tables.

3.Create two new tables:

4.Moving on to similar operations for Table and Table(2), we'll use Table as an example.

5.Expand and fill the field:

6.Filter out the required data.

7.Rename columns.

8.Append two tables:

Result:

Best Regards,
Zhu

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.

[KNP]

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.

[redomer44]

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