Excel Power Query Tutorial - Transforming Nested Tables

Transforming nested tables with Excel Power Query is the focus of Part 6 of my Power Query tutorial series. In this video I demonstrate how to use power query nested tables to enhance the power of your data analyses by creating new group-based columns (i.e., "features").

-----------------------------------------------------------------------------------------------------------------------
Video Resources
-----------------------------------------------------------------------------------------------------------------------
Get the Excel workbook used in this video:

-----------------------------------------------------------------------------------------------------------------------
Additional Power Query Tutorial Videos
-----------------------------------------------------------------------------------------------------------------------
What is Excel Power Query AND Why it Matters to You! (Part 1):

Excel Power Query - Add Column From Examples (Part 2):

Excel Power Query - Add Custom Column 1 (Part 3):

Excel Power Query - Add Conditional Column (Part 4):

Excel Power Query - Grouping Tables of Data (Part 5):

-----------------------------------------------------------------------------------------------------------------------
Excel Analytics Training
-----------------------------------------------------------------------------------------------------------------------

💥 Learning R programming is easy for Excel users! 💥

Stay healthy and happy data sleuthing!

#ExcelPowerQuery #PowerQueryTutorial #PowerQuery #PowerQueryNestedTables

[DaveOnData]

👩‍🔬👨‍🔬 Learn to use your basic Excel skills to analyze the business like a Facebook data scientist:

[TheHiamet]

This is a beautiful service, David. Thank you for that. This series has been beneficial.

[dougmphilly]

just discovered your channel love finding new excel tips. subscribed.

[eunicen]

Great stuff David. You have helped me alot !!!

[razorscythe]

excellent stuff. Please keep posting more

[betinarubianes]

I just wanted to say that I love the content on your channel. You have gained a new subscriber... Thank you! :)

[controlsgirl]

So awesome following these videos!!!! I love love love it!

[raraluna]

on point! looking forward to the next one!

[panduislami]

Excellent, Dave. This is like one hot encoding, correct me if I'm wrong

[RaymondTatani]

Hi, I have an assessment day tomorrow on a claim analyst role, your videos have been super helpful, your joy in teaching makes it fun to learn too, I know this video is from ages ago but you're doing a great service. Also I wanted to ask, I am currently trying to use the "add column from example" but I'm on a mac and I don't have those capabilities. I managed to get around it by using the conditional column, but that hit it limitations when it came to extracting the titles from the names as it couldn't differentiate well between the "Mr" in "Mrs" not being the same as "Mr" any tips on how to get around this problem in its entirety?

[Data_Mom]

Awesome explanation, especially Pivot Column one. if possible David Langer, one separate video on Pivot/unpivot Columns, I get confused with the topic.

[guguminhoto]

Awesome video! I’m trying to import a table from the web that has nested table on it, excel even highlights it for me.
Unfortunately excel is not allowing me to click on “expand”. Any idea on why this might be happening? Thank you!!

[bangheadwall]

I have only 2 columns A and B, A has nested tables for it under B. All i want to do is transpose from row to columns with the nested tables showing up under the relevant number from column A. A new column for each unique value in column A. This is proving way harder than I thought it would be. Any ideas?

[PillsLifestyleReviews]

8:15 OMG I gotta figure out how to do that in python!

[einyv]

So I have a data set trying to match on a huge data on a column I created in each data set. Now in my table where the population is I created 2 column. One for a first pass to find matches and a Second for a second pass. Is there a way to tell power query it it doesn't find a match on the first pass to use the 2nd field instead for the 2nd pass?
If it was SQL I would just join in the lookup source 2x with a condition where it's not found on the first join and encapsulate the column being pulled with isnull(). Does that exist in PQ? For now I created references to the and additional merge query but trying to see if there is a better way.

[KoolGuyism]

I have a huge data base in json file format. There are a lot of lists and records and many lists and records contain more list and records in it. Its a tiring and irritating task to expand more than 50 lists and records manually one by one. This also creates a lot of duplicate records.
So, How to expand multiple lists and records together in power query.?

[VastCNC]

I've been having a problem with nested tables from an OData feed. I have an indicator of month number, year, and company, then the nested table colunn. Filter to the year I need, expand the nested table, load, then None of my expanded data is shown. Just the original filtering columns. I can expand and load a single table, but it breaks with more than one. Scratching my head and digging around when I noticed this.

[casper]

How do you collapse down all the Table and convert to excel again?