I have the kind excel file with double header and need help transforming structured table with single header.Here excel Data below Column1Column2Column3Column4Column5Column6Column7Column8Column9Column10House plan SystemGoal 1 Goal 2 MetricsActualTargetMetric #Total #MetricsActualTargetMetric #Total #% of Objectives met.0%TBD03% of Objectives met.0%TBD04% of Objectives me by original date.0%TBD03% of Objectives me by original date.0%TBD04% of Objectives me by adjusted date.0%TBD03% of Objectives me by adjusted date.0%TBD04% of Action Step met.6%TBD116% of Action Step met.38%TBD616% of Action Step met by original date6%TBD116% of Action Step met by original date31%TBD516% of Action Step met by adjusted date0%TBD016% of Action Step met by adjusted date6%TBD116% of Action Steps revised.25%TBD416% of Action Steps revised.6%TBD116Goal 3 Goal 4 MetricsActualTargetMetric #Total #MetricsActualTargetMetric #Total #% of Objectives met.0%TBD03% of Objectives met.0%TBD04% of Objectives me by original date.0%TBD03% of Objectives me by original date.0%TBD04% of Objectives me by adjusted date.0%TBD03% of Objectives me by adjusted date.0%TBD04% of Action Step met.6%TBD116% of Action Step met.38%TBD616% of Action Step met by original date6%TBD116% of Action Step met by original date31%TBD516% of Action Step met by adjusted date0%TBD016% of Action Step met by adjusted date6%TBD116% of Action Steps revised.25%TBD416% of Action Steps revised.6%TBD116Goal 5 House plan System totals MetricsActualTargetMetric #Total #MetricsActualTargetMetric #Total #% of Objectives met.0%TBD03% of Objectives met.0%TBD04% of Objectives me by original date.0%TBD03% of Objectives me by original date.0%TBD04% of Objectives me by adjusted date.0%TBD03% of Objectives me by adjusted date.0%TBD04% of Action Step met.6%TBD116% of Action Step met.38%TBD616% of Action Step met by original date6%TBD116% of Action Step met by original date31%TBD516% of Action Step met by adjusted date0%TBD016% of Action Step met by adjusted date6%TBD116% of Action Steps revised.25%TBD416% of Action Steps revised.6%TBD116

Regular Visitor

1 year ago

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

Need Help

redomer44
1 year ago

7 Replies

KNP

Super User

1 year ago

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

Regular Visitor

1 year ago

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

KNP
Super User
1 year ago
What is your desired output?
- redomer44
  Regular Visitor
  1 year ago
  - Anonymous
    Not applicable
    1 year ago
    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.
    
    Data shaping cleaning.pbix38 KB
Ashish_Mathur
Super User
1 year ago
Hi,

Paste that data in an MS Excel file and in another tab of that file, show the expected result.
- redomer44
  Regular Visitor
  1 year ago