Excel Magic Trick 1257: Power Query to Create One Proper Table from 10 Cross Tabulated Tables

Learn how to use Power Query to Create One Proper Table from 10 Cross Tabulated Tables. Learn how to Create Excel Tables in Excel, Name the Tables, Import them into Power Query, Unpivot the date columns, Append the tables into one large table and convert text dates to proper dates.

[sherifelgamal]

ExcelIsFun is my favorite Channel! and Power Query one of my favorite topics, Thank you !


We can also do this with fewer clicks and in only one query without having to import each table in a separate query but with more typing in the power query advanced editor.
After setting up the tables in the workbooks use this code:


let
Source = Excel.CurrentWorkbook(), //Gets all the tables in the workbook//
#"Reordered Columns" = Table.ReorderColumns(Source, {"Name", "Content"}),
#"Filtered Rows" = Table.SelectRows(#"Reordered Columns", each Text.Contains([Name], "2015")), //Filter for data tables only because when this query is loaded to a table in the workbook it will circle back and appear in the source whenever the query is updated//
#"Added Custom" = Table.AddColumn(#"Filtered Rows", "Unpivot", each Table.UnpivotOtherColumns([Content], {"Species"}, "Attribute", "Value")), //Add Column to unpivot all tables in content column before expanding//
#"Removed Columns" = Table.RemoveColumns(#"Added Custom", {"Content"}), //Remove extra column containing table names//
#"Expanded Unpivot" = Columns", "Unpivot", {"Species", "Attribute", "Value"}, {"Species", "Attribute", "Value"}), //Expanded contents//
#"Renamed Columns" = Unpivot", {{"Attribute", "Date"}}), //Clean up and format//
#"Changed Type" = Columns", {{"Date", type date}, {"Value", Int64.Type}, {"Species", type text}}),
#"Removed Columns1" = Table.RemoveColumns(#"Changed Type", {"Name"})
in
#"Removed Columns1"

And pray it works:)

[BillSzysz]

Great !! Thanks for PQ lesson :-)
"May the Force be with you" Mike :-)

[abhaygadiya]

Nice Video. Just a tip, you could select species column > right click > unpivot other columns. It will save selecting other date columns every time.

[Jung-samanhudisound]

Thank you Mike for Power Query lesson. The video alone is very helpful; more than that you also proviced the file; thus speeding up learning.

[OzduSoleilDATA]

Very nice. Seeing that M code shortcut is helpful, as a way to bypass manual appending one query at a time.

[natesh]

Wow this was awesome!! I was working on a similar report now i guess i can complete with this method:)

[ExcelStrategy]

To bypass the repetitive procedure of creating a new query from a table without using the ugly shortcut, we can simply put the "From Table" button in the quick access toolbar, maybe in the first or second place, and use the ALT+1 or ALT+2 shortcut !

[PERUTICA]

Great!! You deserve more "likes" :)

[deninsrmic]

Great tutorial Mike, as always. I have been going through many of yours Power Query (append) Videos especially the one using =Excel.CurrentWorkbook(). Data I am working on this function would be brilliant time saver if it worked. Indeed, I would have cut the time tremendously appending tables if this function worked. I am using office 365 Excel but data dates back from Excel 97-2003. Now I assume that this could be the reason why above mentioned function doesn’t work as it gives me instead of data ‘null’ values in some of the columns even though there are not null values in Excel table. I also realised that I am “allowed” to append 10-15 tables until it runs out of memory and the whole system breakers down. Have you encounter this problem yourself. I went also to Microsoft website in order to enquire some solution but couldn’t find any. Be as it may, it was great watching your tutorial again Mike. Would it be possible to furnish a excel formula nesting tutorial? I know we do come across lot of examples nesting formulas in your tutorials. However, I thought to have assiduously explained tutorial in one video. Would that be possible? Many thanks for your engagement and effort creating these tutorials.

[pmsocho]

That seems to be a good challenge for Power Query master Bill Szysz :)

[RambozoClown]

Power Query to the rescue. If this was an Excel duel with Mr.Excel, I wonder what the VBA solution would look like.

[davidbriggs]

You can Duplicate a Query --- which means that you can create the first Query for January2015 and then duplicate the Query and change the data Source.  In this manner, I was able to quickly create the other 9 queries.

[ExcelInstructor]

6:10 - what abut the spaces on the date column? does the date transformation later on just removes spaces?

[MrSarky]

Hey Mike,

I just wanted to ask you: it seems that I'll be able to write my thesis about excel (nothing exact yet). I would really-really like to highlight you and Daniel from ExcelVBAisfun as my biggest influences and how much I've learned from you guys about excel and VBA programming (and continuing to do so). I hope this is okay with you :)

Have an awesome day and take care !

[veganize]

Will the final table be dynamic (I.e. if data is changed in the source tables)? Are they still connected, or does this procedure actually create a new/unlinked table based on the source data?

[parshurambhave]

Can we just copy the Mcode to the second table onwards and only rename the source table instead of repeating all the steps?

[pennypeavy]

Do you have an email address because I have a few questions to ask?

[AnandKumar-xfro]

Hi Sir and all the viewer,

Hope you all are doing well !!

I have one query related with with survey 1 video.

If in excel data is mentioned like below

1 A
2 B
3 C
4 A
5 B
6 C
7 A
8 B
9 C
99

Then how to get data like below in excel or which formula can help us to get below format data

A B C
1 2 3
4 5 6
7 8 9
that....

Could you please help on this ASAP if possible?


Regards,

Anand