cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
cgkas Member
Member

Merge multiple sheets with similar structure in a single table from Excel Workbook

Hi to all,

 

Having data (not saved as tables) in multiple sheets in a workbook, each one with format like shown below. I want to consolidate all pairs of column of each sheet in a single pair of columns in one sheet, for exmaple if there are values in A:B, C: D and E:F I want the data in those pairs of columns in A:B and in 3rd column the sheet name. 

Is possible to do this with M language?

I'm using Excel 2016 but could be applied on Power Query within Excel.  

I import the workbook but and select all sheets, but I don't know how to select even number of columns and then merge the columns.

 

My data is like below (I've attached a sample workbook too here sample.xlsx)

 

Sheet "2434 XYZ" --> Has 9 columns but data useful is in columns that go in pairs, A:B, C: D, E:F. For E:F only first2 rows are useful since 3rd row is not in pair value, I mean, E3 doesn't have an F3 value.

 

2434 XYZ2434 XYZ2434 XYZ2434 XYZ2434 XYZ2434 XYZ2434 XYZ2434 XYZ2434 XYZ
131924530171717   
240135146784112   
34894652719    

 

Sheet "7735" --> Has 8 columns but data useful is again columns that go in pairs, A:B, C: D

 

77357735773577357735773577357735
1377371292    
2194284769    
31611 8668    
44153      
57790      
65442      

 

Sheet "8873" --> Has 8 columns and data useful is A:B, C: D

 

8873887388738873887388738873
1112247101   
2564852213   
3909361112   

 

Output desired is like this in a single sheet(table):

 

131922434 XYZ
240132434 XYZ
348942434 XYZ
453012434 XYZ
514672434 XYZ
652712434 XYZ
717172434 XYZ
841122434 XYZ
137737735
219427735
316117735
441537735
577907735
654427735
712927735
847697735
111228873
256488873
390938873
471018873
522138873
611128873

 

Thanks in advance for any help.

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: Merge multiple sheets with similar structure in a single table from Excel Workbook

Hi @cgkas ,

We could achieve your desired output in Query editor, but that should be a little complex.

Please follow the steps. I will show the table 8873 as an example.

1. Assuming that you load the data in Query editor like below.

step1.PNG

2. Remove the first row and duplicate the table

3. Remove the columns for the two tables and then append queries.

append queries.png

4. Add a custom column with the formula below.

#"Changed Type"[Column1]{0}

Capture.PNG

5. Please do the same modification for the other two tables and then Append the three tables.

Here is the final output.

output.PNG

For the detail steps, you could refer to the applied steps in Query Editor from my attachment.

Best  Regards,

Cherry

 

Community Support Team _ Cherry Gao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

4 REPLIES 4
cgkas Member
Member

Re: Merge multiple sheets with similar structure in a single table from Excel Workbook

Hi, someone have and idea how to do it?

 

Thanks

Community Support Team
Community Support Team

Re: Merge multiple sheets with similar structure in a single table from Excel Workbook

Hi @cgkas ,

We could achieve your desired output in Query editor, but that should be a little complex.

Please follow the steps. I will show the table 8873 as an example.

1. Assuming that you load the data in Query editor like below.

step1.PNG

2. Remove the first row and duplicate the table

3. Remove the columns for the two tables and then append queries.

append queries.png

4. Add a custom column with the formula below.

#"Changed Type"[Column1]{0}

Capture.PNG

5. Please do the same modification for the other two tables and then Append the three tables.

Here is the final output.

output.PNG

For the detail steps, you could refer to the applied steps in Query Editor from my attachment.

Best  Regards,

Cherry

 

Community Support Team _ Cherry Gao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

cgkas Member
Member

Re: Merge multiple sheets with similar structure in a single table from Excel Workbook

Hello Cherry,

 

Thanks for answer.

 

Would be possible for you to share the code of steps, I cannot open pbix since I'm using Excel 2016.

 

Does your solution need to predifined each sheet content as table? because could be more than 100 sheets and they are not defined as tables.

 

Thanks again.

Community Support Team
Community Support Team

Re: Merge multiple sheets with similar structure in a single table from Excel Workbook

Hi @cgkas ,

Please reference the M query below.

Spoiler

shared Table = let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WsrAwN1bSIZqK1YlWMgRyDA2NjICUCRCbGxqARBTgGKQGJGlqZmIBooDYyMjQGEMNSMTSwBJEmYGNNATpgqLYWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t, Column4 = _t, Column5 = _t, Column6 = _t, Column7 = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", Int64.Type}, {"Column2", Int64.Type}, {"Column3", Int64.Type}, {"Column4", Int64.Type}, {"Column5", Int64.Type}, {"Column6", Int64.Type}, {"Column7", Int64.Type}}),
#"Removed Top Rows" = Table.Skip(#"Changed Type",1),
#"Removed Other Columns" = Table.SelectColumns(#"Removed Top Rows",{"Column1", "Column2"}),
#"Appended Query" = Table.Combine({#"Removed Other Columns", #"Table (2)"}),
#"Added Custom" = Table.AddColumn(#"Appended Query", "Custom", each #"Changed Type"[Column1]{0}),
#"Changed Type1" = Table.TransformColumnTypes(#"Added Custom",{{"Column1", type text}, {"Column2", type text}, {"Custom", type text}})
in
#"Changed Type1";

shared Table2 = let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("tYyxDYAwDARXiVKnyNsmTjYBouy/Bn4KKKiRfLL1Ov+cWUwt7ceZy0/nKjMjAsWQWBZsWpl4AAdXeqB+exVKlY41Op0pIB+dnvXB5sYXcbaP4J21Lg==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t, Column4 = _t, Column5 = _t, Column6 = _t, Column7 = _t, Column8 = _t, Column9 = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}, {"Column5", type text}, {"Column6", type text}, {"Column7", type text}, {"Column8", type text}, {"Column9", type text}}),
#"Removed Top Rows" = Table.Skip(#"Changed Type",1),
#"Removed Other Columns" = Table.SelectColumns(#"Removed Top Rows",{"Column1", "Column2"}),
#"Appended Query" = Table.Combine({#"Removed Other Columns", #"Table2 (2)", #"Table2 (3)"}),
#"Added Custom" = Table.AddColumn(#"Appended Query", "Custom", each #"Changed Type"[Column1]{0}),
#"Changed Type1" = Table.TransformColumnTypes(#"Added Custom",{{"Column1", type text}, {"Column2", type text}, {"Custom", type text}}),
#"Appended Query1" = Table.Combine({#"Changed Type1", Table1, Table})
in
#"Appended Query1";

shared Table1 = let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("lY5LCsAwCESvUlxn0UmM0bOE3P8a1baLfkKh4DA6PsTeqbVSKf21kTrBh+JTZC5ky27LTcFFCuMwdXETm3JxCAKcoYrolOM4glpeyyt0fGrrJyTeVd4/e9YYGw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t, Column4 = _t, Column5 = _t, Column6 = _t, Column7 = _t, Column8 = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", Int64.Type}, {"Column2", Int64.Type}, {"Column3", Int64.Type}, {"Column4", Int64.Type}, {"Column5", Int64.Type}, {"Column6", Int64.Type}, {"Column7", Int64.Type}, {"Column8", Int64.Type}}),
#"Removed Top Rows" = Table.Skip(#"Changed Type",1),
#"Removed Other Columns" = Table.SelectColumns(#"Removed Top Rows",{"Column1", "Column2"}),
#"Appended Query" = Table.Combine({#"Removed Other Columns", #"Table1 (2)"}),
#"Added Custom" = Table.AddColumn(#"Appended Query", "Custom", each #"Changed Type"[Column1]{0}),
#"Changed Type1" = Table.TransformColumnTypes(#"Added Custom",{{"Column1", type text}, {"Column2", type text}, {"Custom", type text}})
in
#"Changed Type1";

shared #"Table1 (2)" = let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("lY5LCsAwCESvUlxn0UmM0bOE3P8a1baLfkKh4DA6PsTeqbVSKf21kTrBh+JTZC5ky27LTcFFCuMwdXETm3JxCAKcoYrolOM4glpeyyt0fGrrJyTeVd4/e9YYGw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t, Column4 = _t, Column5 = _t, Column6 = _t, Column7 = _t, Column8 = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", Int64.Type}, {"Column2", Int64.Type}, {"Column3", Int64.Type}, {"Column4", Int64.Type}, {"Column5", Int64.Type}, {"Column6", Int64.Type}, {"Column7", Int64.Type}, {"Column8", Int64.Type}}),
#"Removed Top Rows" = Table.Skip(#"Changed Type",1),
#"Removed Other Columns" = Table.SelectColumns(#"Removed Top Rows",{"Column3", "Column4"}),
#"Renamed Columns" = Table.RenameColumns(#"Removed Other Columns",{{"Column3", "Column1"}, {"Column4", "Column2"}}),
#"Filtered Rows" = Table.SelectRows(#"Renamed Columns", each ([Column1] <> null))
in
#"Filtered Rows";

shared #"Table (2)" = let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WsrAwN1bSIZqK1YlWMgRyDA2NjICUCRCbGxqARBTgGKQGJGlqZmIBooDYyMjQGEMNSMTSwBJEmYGNNATpgqLYWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t, Column4 = _t, Column5 = _t, Column6 = _t, Column7 = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", Int64.Type}, {"Column2", Int64.Type}, {"Column3", Int64.Type}, {"Column4", Int64.Type}, {"Column5", Int64.Type}, {"Column6", Int64.Type}, {"Column7", Int64.Type}}),
#"Removed Top Rows" = Table.Skip(#"Changed Type",1),
#"Removed Other Columns" = Table.SelectColumns(#"Removed Top Rows",{"Column3", "Column4"}),
#"Renamed Columns" = Table.RenameColumns(#"Removed Other Columns",{{"Column3", "Column1"}, {"Column4", "Column2"}})
in
#"Renamed Columns";

shared #"Table2 (2)" = let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("tYyxDYAwDARXiVKnyNsmTjYBouy/Bn4KKKiRfLL1Ov+cWUwt7ceZy0/nKjMjAsWQWBZsWpl4AAdXeqB+exVKlY41Op0pIB+dnvXB5sYXcbaP4J21Lg==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t, Column4 = _t, Column5 = _t, Column6 = _t, Column7 = _t, Column8 = _t, Column9 = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}, {"Column5", type text}, {"Column6", type text}, {"Column7", type text}, {"Column8", type text}, {"Column9", type text}}),
#"Removed Top Rows" = Table.Skip(#"Changed Type",1),
#"Removed Other Columns" = Table.SelectColumns(#"Removed Top Rows",{"Column3", "Column4"}),
#"Renamed Columns" = Table.RenameColumns(#"Removed Other Columns",{{"Column3", "Column1"}, {"Column4", "Column2"}})
in
#"Renamed Columns";

shared #"Table2 (3)" = let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("tYyxDYAwDARXiVKnyNsmTjYBouy/Bn4KKKiRfLL1Ov+cWUwt7ceZy0/nKjMjAsWQWBZsWpl4AAdXeqB+exVKlY41Op0pIB+dnvXB5sYXcbaP4J21Lg==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t, Column4 = _t, Column5 = _t, Column6 = _t, Column7 = _t, Column8 = _t, Column9 = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}, {"Column5", type text}, {"Column6", type text}, {"Column7", type text}, {"Column8", type text}, {"Column9", type text}}),
#"Removed Top Rows" = Table.Skip(#"Changed Type",1),
#"Removed Other Columns" = Table.SelectColumns(#"Removed Top Rows",{"Column5", "Column6"}),
#"Filtered Rows" = Table.SelectRows(#"Removed Other Columns", each ([Column6] <> "")),
#"Renamed Columns" = Table.RenameColumns(#"Filtered Rows",{{"Column5", "Column1"}, {"Column6", "Column2"}})
in
#"Renamed Columns";

In addition, I have tested based on table format.

Best  Regards,

Cherry

 

Community Support Team _ Cherry Gao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
Ask Amanda Anything Q&A

Ask Amanda Anything Q&A

Learn the answers to some of the questions asked during the Amanda Triple A event.

October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 187 members 1,871 guests
Please welcome our newest community members: