Check out our newly launched M Language course ↗ - goodly.co.in/learn-m-powerquery/
@WHATuWATCHisWHATuGET
2 жыл бұрын
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
2 жыл бұрын
Glad you liked it!
@pravinshingadia7337
Жыл бұрын
Easily one of the best Power Bi guys here. Love the techniques you show.
@GoodlyChandeep
Жыл бұрын
Glad you think so!
@ankursharma6157
3 ай бұрын
My Token of Gratitude! This PQ series is helping Me immensely 🙏🏽 THANK YOU!
@arne.munther
11 ай бұрын
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
3 жыл бұрын
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 KZitem algorithm and helps in increasing the visibility of the channel.
@GoodlyChandeep
3 жыл бұрын
Thanks Rishabh for the kind words :) I am glad you enjoyed the videos.
@jnsjcnaocndicnapidcn
2 күн бұрын
Great video. Just use it and work perfectly. Thanks!
@wayneedmondson1065
3 жыл бұрын
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!!
@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.
@gennarocimmino
2 жыл бұрын
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
2 жыл бұрын
Glad you like it !
@mathewinmuscat
Жыл бұрын
Super, Super, Super..... Thanks a ton Chandeep. You are a GEM!!!!
@andrewferguson7762
3 жыл бұрын
what a great video....learned masses and now subscribed.
@mergeop
2 жыл бұрын
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
2 жыл бұрын
Super!
@chakrabmonoj
2 жыл бұрын
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
2 жыл бұрын
I think the underlying data could have changed.. I can't say much unless I see it.
@chakrabmonoj
2 жыл бұрын
@@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
@aliramadan7425
3 жыл бұрын
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.
@swannous
7 ай бұрын
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
@seunoyin4706
23 күн бұрын
This is awesome. Can you as well do for a sharepoint folder for automation?
@danielvolgan
2 жыл бұрын
Great, thanks. I used the same technique to unpivot first then aggregate files from a folder.
@GoodlyChandeep
2 жыл бұрын
Glad it was useful 💚
@lucianoriquet8552
Жыл бұрын
I am amazed. Thanks for the lesson
@KgasS
2 жыл бұрын
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.
@ThoughtMatters_MissionBillion
2 жыл бұрын
very clear explanation. thank you so much
@Simon-vc1wk
2 жыл бұрын
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.
@darshantalreja1312
3 жыл бұрын
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
2 жыл бұрын
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.
@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?
@AWBL
3 жыл бұрын
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
3 жыл бұрын
Yes it will!
@tateghoul
Ай бұрын
great video, sometimes but we need them in text form too, create a proper blog
@ThuThuyDo0403
3 жыл бұрын
This is very informative. Thank you!
@taylorhernandez3038
2 жыл бұрын
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
@HieuPham-su2wn
2 жыл бұрын
Super nice video. Can you please make a tutorial on doing this when loading files from folder?
@thamizhansudip6644
2 жыл бұрын
super awesome it's an Advance level...genious 👏
@GoodlyChandeep
2 жыл бұрын
Thanks a lot 😊
@info2satish
3 жыл бұрын
Brilliant trick... awesome video!!🙏👍😀
@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?
@lionels839
Жыл бұрын
Is it possible to dynamically remove errors and unpivot before expanding a nested table?
@ANUPkumar-jz6yf
3 жыл бұрын
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
3 жыл бұрын
This would be helpful, I am struggling with this aswell
@nikahafiz1
3 жыл бұрын
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).
@kulpreetsingh9064
3 жыл бұрын
Can we get the same dataset to play with!
@janiszeh
3 жыл бұрын
Thanks a lot, very useful video !
@office4u791
3 жыл бұрын
clear as crystal, thanks
@hermesjrd
3 жыл бұрын
Thanks a Lot, Chandeep..
@ramachandran7760
3 жыл бұрын
Eagerly waiting for new videos 👍
@AzharKhan-kd9bf
Жыл бұрын
Hi Chandeep, could you share the excel file that contains M code related to the example above
@kebincui
3 жыл бұрын
thanks Goodly
@ianferrus
3 жыл бұрын
so good explaination!
@luigiseven
3 жыл бұрын
Thanks, that was really good
@lokesh221188
3 жыл бұрын
Awesome work 👍🏻
@pavankumarreddy189
3 жыл бұрын
superb chandeep
@kulpreetsingh9064
3 жыл бұрын
Very informative
@vinod71234
3 жыл бұрын
Sir we can varible write in excel power query Please help??
@abdulmuyeed230
Жыл бұрын
Great If all sheets are not tables. How to convert all sheets into tables to load into editor
@maksim0933
2 жыл бұрын
excellent!
@pallabihazarika6101
Ай бұрын
How to add get data option in excel
@raimundojs9547
Жыл бұрын
Perfect!
@sravankumar1767
3 жыл бұрын
nice explanation..
@migmanc
3 жыл бұрын
Very good video, if you could speak a little slower will be great!!
@mrishabh
3 жыл бұрын
Nah, I like him this way
@MrTerenceJonathan
11 ай бұрын
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
Пікірлер: 74