Unpivot and Combine Data from Multiple Tables using Power Query

Check out our newly launched M Language course ↗️ - goodly.co.in/learn-m-powerquery/
- - - - My Courses - - - -
✔️ Mastering DAX in Power BI -
goodly.co.in/learn-dax-powerbi/
✔️ Power Query Course-
goodly.co.in/learn-power-query/
✔️ Master Excel Step by Step-
goodly.co.in/learn-excel/
✔️ Business Intelligence Dashboards-
goodly.co.in/learn-excel-dash...
- - - Blog - - -
www.goodly.co.in/blog

[@GoodlyChandeep]

Check out our newly launched M Language course ↗ - goodly.co.in/learn-m-powerquery/

[@WHATuWATCHisWHATuGET]

man, this might just be the most clear, to the point, informative, well-explained tutorial I've seen yet. Your efforts are truly appreciated.

[@GoodlyChandeep]

Glad you liked it!

[@arne.munther]

Thanks, that was just the first step to a solution to a problem I am working with, where I have been stock for some days now. Thank you.

[@mrishabh]

Your videos are practical and to the point, just as they should be. You should also remind users to click on the like button as I believe that feeds into the KZbin algorithm and helps in increasing the visibility of the channel.

[@GoodlyChandeep]

Thanks Rishabh for the kind words :) I am glad you enjoyed the videos.

[@pravinshingadia7337]

Easily one of the best Power Bi guys here. Love the techniques you show.

[@GoodlyChandeep]

Glad you think so!

[@wayneedmondson1065]

Awesome Chandeep! Love your tricks and techniques that go beyond the basic PQ interface. In fact, I can put this lesson to good use immediately. Thanks a million :)) Thumbs up!!

[@aliramadan7425]

Thank you so much. This video helped me create a similar solution for automating grabbing data from several sheets in an excel workbook and stacking them all up in one table.

[@mathewinmuscat]

Super, Super, Super..... Thanks a ton Chandeep. You are a GEM!!!!

[@andrewferguson7762]

what a great video....learned masses and now subscribed.

[@ThuThuyDo0403]

This is very informative. Thank you!

[@office4u791]

clear as crystal, thanks

[@info2satish]

Brilliant trick... awesome video!!🙏👍😀

[@ThoughtMatters_MissionBillion]

very clear explanation. thank you so much

[@lucianoriquet8552]

I am amazed. Thanks for the lesson

[@mergeop]

Thank you! Had been trying to get a custom function like this to work for most of the week. Got it to work with two hours to spare :-)

[@GoodlyChandeep]

Super!

[@janiszeh]

Thanks a lot, very useful video !

[@ianferrus]

so good explaination!

[@gennarocimmino]

Kudos for the clarity of exposition. The created function applicable to all worksheets, is really a brilliant use of Power Query. I am very grateful to you. Thank you.

[@GoodlyChandeep]

Glad you like it !

[@MrDhunpagla]

Thanks a Ton !!!!!!🙏

[@hermesjrd]

Thanks a Lot, Chandeep..

[@lokesh221188]

Awesome work 👍🏻

[@thamizhansudip6644]

super awesome it's an Advance level...genious 👏

[@GoodlyChandeep]

Thanks a lot 😊

[@luigiseven]

Thanks, that was really good

[@ramachandran7760]

Eagerly waiting for new videos 👍

[@kebincui]

thanks Goodly

[@kulpreetsingh9064]

Very informative

[@raimundojs9547]

Perfect!

[@pavankumarreddy189]

superb chandeep

[@danielvolgan]

Great, thanks. I used the same technique to unpivot first then aggregate files from a folder.

[@GoodlyChandeep]

Glad it was useful 💚

[@sravankumar1767]

nice explanation..

[@maksim0933]

excellent!

[@KgasS]

Thanks for the nice lessor.This method expects all the data sheet format to be the same. ie Sheet heading such as Sales for the year then two empty rows and data. To avoid the function to fail it is better to work on tables on different sheets rather than sheet contents. Checking the data format will be an additional step before applying the function.

[@swannous]

have a nice day thanks for your excellent tutorials i have one question: i have a pdf source with name changed every time i refresh the date How i can change the source hard coded file name in source to make it dynamic to select the one only file in the folder or select the latest file in folder? regards

[@Ednut19]

Clear an simple.Thank you. Is this more accurately about importing data from multiple tabs in one file? I was hoping for a solution using functions to combine multiple tabs from separate files.

[@HieuPham-su2wn]

Super nice video. Can you please make a tutorial on doing this when loading files from folder?

[@chakrabmonoj]

Hi Chandeep - thanks for the crisp and clear demonstration. This will definitely save a lot of time and steps, especially where a lot a cleaning steps are likely to be involved. While trying to apply this method with two sheets, I have run into a problem with the following error code : = (mysteps as table) => let Source = Excel.Workbook(File.Contents(mysteps), null, true), Table1_Table = Source{[Item="Table1",Kind="Table"]}[Data], #"Changed Type" = Table.TransformColumnTypes(Table1_Table,{{"Reporting Date", type datetimezone}, {"Merchant SKU", type text}, {"Title", type text}, {"Shipped Quantity", Int64.Type}, {"Currency", type text}, {"Item Price", type number}, {"Item Tax", type number}, {"FC", type text}, {"Day", type any}}), #"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"Day"}), #"Split Column by Delimiter" = Table.SplitColumn(Table.TransformColumnTypes(#"Removed Columns", {{"Reporting Date", type text}}, "en-IN"), "Reporting Date", Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.Csv, false), {"Reporting Date.1", "Reporting Date.2"}), #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Reporting Date.1", type date}, {"Reporting Date.2", type time}}), #"Renamed Columns" = Table.RenameColumns(#"Changed Type1",{{"Reporting Date.1", "Date"}, {"Reporting Date.2", "Time"}}) in #"Renamed Columns" If I run this code on a different workbook, the error code I am getting is as below : An error occurred in the ‘’ query. Expression.Error: We cannot convert a value of type Table to type Text. Details: Value=[Table] Type=[Type] Is there something I am getting wrong? Thanks

[@GoodlyChandeep]

I think the underlying data could have changed.. I can't say much unless I see it.

[@chakrabmonoj]

@@GoodlyChandeep Hi Chandeep. Thanks for the quick revert. I can ping over the data to you if you give me your email address. I have a cleaned query in PBI but have received a new update. I can share both the PBIX and the new raw file and it will be a great help if you can walk me thru' the steps to : 1. Upload/Append the new data to the existing query - only the unique rows of data 2. Keep the data/column structure of the existing query 3. Use the DAX measures which I wrote for the current query in the new appended query with additional/new rows of data Much appreciated and apologies for the out-of-whack request

[@santoshpv321]

Too good!

[@GoodlyChandeep]

Thanks!!

[@srenbroner5713]

Great Video. I have seen it at least 30 times, not because its unclear, but because I have some modifications :-) . Im a big fan of how you explain, and break things down, in all your many videos. My Challenge is that i need to merge at folder with CSV files. The building of the funktion works great, and results in a nice table. But the table with the table with tables, tricks me. I have made a two column approch - to convert [Content] Column(Your Data Column) to [Data] Table.AddColumn(Source, "KonverBi", each Csv.Document([Content])) - Returns output - single column, with lots of error And Table.AddColumn(#"Added Custom", "Custom", each Funk_med_OtherMarked([KonverBi])) Error What to do :-) When using CSV Kind Regard Søren

[@inkuban]

- Load at the folder level - do not combine - Use section - convert section to table - add the column of tables with the function to wrangle table What is causing the error?

[@migmanc]

Very good video, if you could speak a little slower will be great!!

[@mrishabh]

Nah, I like him this way

[@AWBL]

Thanks so much, that’s a nifty little trick. Would it work for when querying files from a folder instead of tabs in a workbook and columns with different header names?

[@GoodlyChandeep]

Yes it will!

[@ANUPkumar-jz6yf]

Can you plz implement the same for multiple workbooks which is in CSV format? Here data is in multiple sheets but I want to you to implement the same if we have seperate workbook for each year.

[@theinvestorcircle9892]

This would be helpful, I am struggling with this aswell

[@Simon-vc1wk]

Is there a difference in system resources on using a single function compared to repeating the unpivot actions on each file? The set of files I have are similar to this but their meta data isn't unique. How can prevent duplicates being imported. The files iam using (although I don't control) are a mixture of cumulative ytd and single months and potentially repeated across files.

[@kulpreetsingh9064]

Can we get the same dataset to play with!

[@ahsaaahy7788]

Thank you for another excellent video, however I am struggling with multiple excels with multiple spreadsheet/Tables, while 1st spreadsheet in every excels are identical, and 2nd spreadsheets in every excels are identical, but used to collect different kinds of information between spreadsheets..... With the same logic you showed here, can I group spreadsheet 1, and spreadsheet 2 from all excels files and import as table one with all spreadsheet 1, and table 2 includes all spreadsheet no.2, Also, is there any chance to create a table to show the sum(or other function) of a few columns in all spreadsheet no.3 in all excels without loading all data in all spreadsheets no.3 in all excels..... Seems there isn't any example on this, can you help?

[@lionels839]

Is it possible to dynamically remove errors and unpivot before expanding a nested table?

[@darshantalreja1312]

Dear Chandeep, Excellent video I must say. One question though. I have pivoted sets of data for each month, where one pivoted set has 31 days, the other pivoted set has 30 days, and so on (oh and yes, one of the pivoted data set has 28 days as well) So how do we unpivot these in a dynamic way, all at once, just like you did above? I could think of unpivotting to the extent of number of available columns but then how do you even count the columns in power query? Sorry for the long post, I'm a newbie but I strongly believe in this technology and I am sure you'll have an answer already up your sleeve 🙂🙏

[@EdT1977]

Just choose 'unpivot other columns' and it should work a treat. I had a similar challenge where each file was a new week so new dates so I couldn't specify the column names to unpivot but could use the 'unpivot other columns' option.

[@taylorhernandez3038]

Hi, awesome video! I have completed these steps with my data set but when I create a line chart visual, the months are out of order on the x-axis. I've tried to change the data type of the column that used to be attribute, but Power BI gives an error so I can't do that. Any tips with how to handle this?

[@deda118]

I have the same issue. Any suggestions welcome please

[@nikahafiz1]

hi chandeep, wud it b ok fr me go post on my linkedin page some of yr vdo's, pls? i assume same can also b done for multiple files which have same columns (eg. balance sheet / p+l statements generated from accounting apps).

[@vinod71234]

Sir we can varible write in excel power query Please help??

[@AzharKhan-kd9bf]

Hi Chandeep, could you share the excel file that contains M code related to the example above

[@abdulmuyeed230]

Great If all sheets are not tables. How to convert all sheets into tables to load into editor

[@lokesh221188]

Could you share the solution file please ☺️

[@MrTerenceJonathan]

Hi Chandeep, I need a support on a function. Below is the issue. I want the function to refer Eco year and sum only those 2 years 2023&2024 and provide a value in a new column. I have more than 90k rows of data each has different Eco years but not excedding 20. Can you help me please. Part# - 1234 Eco Years - 2 fcst_2023-17190 fcst_2024-50465 fcst_2025-42616 fcst_2026-32814