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

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

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

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.

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

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.

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

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

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

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

This is very informative. Thank you!

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

very clear explanation. thank you so much

clear as crystal, thanks

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

I am amazed. Thanks for the lesson

Thanks a lot, very useful video !

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.

so good explaination!

Awesome work 👍🏻

Thanks a Lot, Chandeep..

Thanks, that was really good

Thanks a Ton !!!!!!🙏

Eagerly waiting for new videos 👍

Very informative

thanks Goodly

superb chandeep

nice explanation..

Perfect!

excellent!

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

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.

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

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

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.

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

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

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?

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

Too good!

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.

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.

Can we get the same dataset to play with!

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

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?

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

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?

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

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

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

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

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

Could you share the solution file please ☺️