Adding Columns to Nested Table for Data Consolidation

[@dapperlink]

Two solid days I spent trying to do this. I got a freaky solution but was not at all happy with it. I went down this path but that function thing was the key. This is valuable even 5 years later! Thank you!!

[@gospelmoto2833]

Awesome! you nailed what I need. Thanks.

[@raitup00]

Nice explanation, man!

[@CAKimberlyLewis]

BEAUTIFUL! Thank you! 🌞

[@wayneedmondson1065]

Awesome! Just what I needed to solve this problem. Thanks for demonstrating! Thumbs up!!

[@omarsierra3709]

Simply Magic bro, Thanks for this helpful content!!

[@krzyzakpolska]

Amazing video!!!

[@kebincui]

Fabulous!👍👍

[@kennethstephani692]

Great video!,

[@milocookieXD]

This is exactly what I needed! You’re a lifesaver 😀

[@pmsocho]

EXCELlent explanation! Thanks!

[@Bristerireland]

Nice! do you know if there is any performance benefit to using table.combine instead of expanding each table to new rows?

[@ThePowerUser]

this is just personal experience as there isn't really any documentation from Microsoft specifying what's "the best" approach, but Table.Combine is the operation that you want to use if you're trying to combine tables. If you want to combine multiple lists in a single list then you'd use List.Combine, but, overall, I'd recommend Table.Combine over any other way.

[@ThePowerUser]

btw, this is probably the best approach when trying to combine tables: kzbin.info/www/bejne/joG6n4N3nbZ4n7M

[@Bristerireland]

Thanks!

[@ppvilleg7]

Hi, Very nice solution. Is it possible to add multiple columns at once?

[@ThePowerUser]

you could create a custom function and make that happen, yes. Conceptually what you're doing is effectively running a function against a table, so you could create a function beyond the native function Table.AddColumn to make that happen.

[@ppvilleg7]

@@ThePowerUser Thanks

[@ppvilleg7]

​@@ThePowerUser I've been looking for an alternative solution and I've found that Table.ExpandTableColumn() could work but I can't find what is the difference between the solution you propose using Table.Combine() and Table.ExpandTableColumn(). Can you help me?

[@ThePowerUser]

@@ppvilleg7 I'd suggest that you post your full scenario and expected output on the official Power Query forum: community.powerbi.com/t5/Power-Query/bd-p/power-bi-services

[@wmfexcel]

This is brilliant! Thanks for the video! :) 👍👍

[@jillianballard9921]

Excellent - exactly what I needed!

[@arekbombka9750]

Awesome!

[@janakadeshapriyabandara5916]

Very helpful. Thanks

[@BeTheLightToTheWorld]

So helpful! Thank you a lot!

[@davideferrari9333]

Thank you very much for this video. it is very useful. Can you please explicit the meaning of (r)=> [File Name]? how can it change the table context ? how can it "know" the outside Table? I do not understand what is the context in the nested AddColumn and how (r)=> change it. thank you in advance

[@ThePowerUser]

That's a good question! I'd probably refer back to the official documentation so you can have a better understanding of the M language: docs.microsoft.com/en-us/powerquery-m/m-spec-functions The big key here is that each is the same as writing (_)=> Understanding how each works and how you can access things differently than in the "each" context is the to key to it all.

[@davideferrari9333]

Thank you! I eventually found this explicit explanation about the meaning of context environment in M where it is clearly explained how that works. I hope can help! ssbi-blog.de/blog/technical-topics-english/the-environment-concept-in-m-for-power-query-and-power-bi-desktop-part-4/

[@ThePowerUser]

​@@davideferrari9333 nice! The key is understanding what the each and (r)=> do and what they're for. Understanding this also enables you to understand how a function that gets invoked as a custom column bypasses this and how it has its own implicit way to define its scope.

[@Sthudio]

This is exactly what I want to do, but my input is not table but Binary, can I do the same if this is the case? I have want to preserve the folder path column when expanding all the binaries

[@ThePowerUser]

hey! not exactly sure that I follow exactly what you're trying to accomplish with the binaries. I'd recommend transforming the binaries into another data type such as tables, lists, records or just scalars. I'd recommend that you post your scenario on: community.powerbi.com/t5/Power-Query/bd-p/power-bi-services

[@jarir-aljubari6679]

Can I add columns with different functionality same way in nested tables?

[@ThePowerUser]

it depends! but the concept of adding tables to nested tables is possible. it just depends on what type of columns you want to add or how you want to add them.

[@farazshaikh74]

Hi.. thanks a lot for the video.. could you please help how to do the grouping running total inside this table.. note : I don't want use custom function..

[@ThePowerUser]

Hey! Could you please post your full scenario on the official Power Query forum? The KZbin comment section is probably not the best way to give help you on this topic, but that forum sure is. Please post the link to your thread here once its public

[@ExcelWithChris]

How do I grab a column from another table into this one

[@ThePowerUser]

you could potentially use QueryName[ColumnName] More info in the official documentation: learn.microsoft.com/en-us/powerquery-m/m-spec-basic-concepts

[@frankling.zevallosestrada5965]

lo veo desde peru deberías ponerle subtitulos en español. gracias

[@ThePowerUser]

Hola Franklin! mis disculpas. Espero dentro de poco poder crear un nuevo canal de KZbin dedicado al español. Saludos!

[@Alex-uh5qv]

i want you see with million rows how you will add all columns names there...

[@ThePowerUser]

the rows don't really matter that much as we could create methods that could basically just get the column names without evaluating the rows with functions like Table.ColumnNames. Now, if you want to add those new column names to a lot of rows then you'll have to pay that price somehow, but there are many ways to achieve the same solution and this is just one of those. Albeit a niche method, its the method that was requested by one of my subscribers at the time.