Unpivot and Combine Data from Multiple Tables using Power Query 

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

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

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.

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

This is awesome. Can you as well do for a sharepoint folder for automation?

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

great video, sometimes but we need them in text form too, create a proper blog

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

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).

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 RU-vid algorithm and helps in increasing the visibility of the channel.

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

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

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

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!!

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

How to add get data option in excel

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

My Token of Gratitude! This PQ series is helping Me immensely 🙏🏽 THANK YOU!

Can we get the same dataset to play with!

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.

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?

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

thanks Goodly

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.

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.

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

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

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.

excellent!

Perfect!

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.

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

very clear explanation. thank you so much

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

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

Could you share the solution file please ☺️

This is very informative. Thank you!

Thanks a lot, very useful video !

I am amazed. Thanks for the lesson

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.

Eagerly waiting for new videos 👍

Thanks, that was really good

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 🙂🙏

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?

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?

Thanks a Lot, Chandeep..

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

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 :-)

so good explaination!

clear as crystal, thanks

Very informative

nice explanation..

Too good!

superb chandeep

Awesome work 👍🏻

Thanks a Ton !!!!!!🙏

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