Good stuff, Chandeep! Now, I would use a template for all customers, put the files in a folder and work from there, avoiding the issue all together. For several input streams towards, I would make 1 folder + query per stream and combine the streams manually down the -er- flow. Still a good trick and good to know.
wonderful brother!!! a few days ago I was wondering how to get the name of the query in another query and you just did it; You are a teacher; I was working with a file where the tables are on different sheets and I needed to do a summary in the same book but I didn't want to load tables separately... now I'm going with this improvement. Thank you!!!
In case of I have a table from query naming Query_Tab1 in excel file 1. Then I add the extra some column beside of Query_Tab1. I want to get the new data, including the Q_T1 and news extra column to a new table nam wing Tab2. Then combine Tab2 to my report.. Many thanks
Great video. Btw, how do I load all tables (in one sheet or multiple sheets) in Excel into PQ in one swoop, without checking one by one in Navigator Select Multiple Items?
Very good - your videos are avid watching. Could you do a video on automating sorting columns. So have multiple tables with month and year columns which after being appended produce one table but columns are not in month date order. How could you automate this?
This is good, however how to select just one of the available queries? I am sure you could filter for just one of the tables in the list. Some time ago, I found a way (worked with Ken Puls on this) to programatically select a query by its name: (this is in a function). Let me know what you think. (qName) => let qName2=Expression.Identifier(qName), Source = Expression.Evaluate(qName2,#shared) in Source
I found it very useful and I am really looking forword to learn more but not understand the video fully due to the English language could you please explain it in Marathi or Hindi.
I think creating custom M function using one data source then putting all the files in folder and importing it PQ and invoke custom function on selected files may be alternative solution.
Numeric distribution - How to create measure on multi level also if sum of qty is negative it will be ignore, i have the column like year, month, region, branch, store code, date, product code, distributor code, Sales qty and value, plz help
Dear Friend. I have one problem. I have this worksheet im developing for my Menu Costing. There's about 60 menus and it might keep expanding with time, and each menu i made on sheet of each with same template of sheet with one another. Each sheet have fixed position of data. Example: Menu Costing, Suggested selling price, current selling price, margin status bad or good. Then i want to make one Summary Sheet which i can view all these data from one master sheet. How can i draw data from this multiple sheet into one master sheet with the challenge the sheet might expand in the future and i want it to automatically insert data to the master list
Hi @Candeep, I have loved your way of solving so may queries and your explanations. Could you please help on the below Query. Actually the Query is written in Tableau. I need to wright the same logic in Power BI. (in DAX or 'M' Language.) I have Struck in my work. please help me on the same @Candeep If [row_number] = 0 then upper((trim( split (Trim( SPLIT( [COLUMN_NAME], "||",1)),":",1)))) else (trim( split (TRIM( SPLIT([COLUMN_NAME],"||",1)), ":", 2))) END If [row_number] = 0 then upper((trim( split (Trim( SPLIT( [COLUMN_NAME], "||",2)),":",1)))) else (trim( split (TRIM( SPLIT([COLUMN_NAME],"||",2)), ":" ,2))) END If [row_number] = 0 then upper((trim( split (Trim( SPLIT( [COLUMN_NAME], "||",3)),":",1)))) else (trim( split (TRIM( SPLIT([COLUMN_NAME],"||",3)) ,":", 2))) END If [row_number] = 0 then upper((trim( split (Trim( SPLIT( [COLUMN_NAME], "||",4)),":",1)))) else (trim( split (TRIM( SPLIT([COLUMN_NAME],"||",4)), ":", 2))) END If [row_number] = 0 then upper((trim( split (Trim( SPLIT( [COLUMN_NAME], "||",5)),":",1)))) else (trim( split (TRIM( SPLIT([COLUMN_NAME],"||",5)), ":", 2))) END etc..................
*Note - this doesn't work in Power BI but only in Excel PQ environment.*
Is there a reason it does not work in PBI?
By the way this will improve my ETL significantly. Thanks for sharing.
Why don't you come up with full M code tutorial
Fantastic tips! This is great.
It seems to work perfectly in Power BI for me?
Oh I see - it doesn't load to the data model. I wonder why!
do you have a similar aproach that can work for PowerBI?
You are one of the most productive content creator in this platform , I learned so much from you. Thank you
Excellent video, never imagined this would be even a thing, and yet you're once again diving deep into it!
Great video; I can see so many cases where this will be a useful tool to have.
This is awesome! I love how dynamic this is!
There is no doubt. This is superb coool and awesome overloaded. Thank you.
Thank for sharing and explaining that very helpful „section“
This is very useful in daily work. Thank you Chandeep!!!
Flipping FANTASTIC!!!!! Thanks so much for this. This will help in so many ways. Great job!
Cool of course it will help us... Super Awesome!!!
Thank You!
I am always always learning something new and different with your videos. This is great. 👍🏽
Amazing and very very usefull solution. Thanks a lot, Mr Chandeep :)
Good stuff, Chandeep!
Now, I would use a template for all customers, put the files in a folder and work from there, avoiding the issue all together.
For several input streams towards, I would make 1 folder + query per stream and combine the streams manually down the -er- flow.
Still a good trick and good to know.
Great information ℹ️ Ypur content is very valuable. I appreciated. Thank you.
Glad you think so!
आप पावर क्वेरी में एम कोड सीखने का आसान रास्ता बना रहे हो 😊
You are GREAT
Thanks, wery nice 👍 Dynamizing is always cool!
very very gooooood. I love it.
Glad you like it!
Great video, Chandeep, as usual!!
wonderful brother!!! a few days ago I was wondering how to get the name of the query in another query and you just did it; You are a teacher; I was working with a file where the tables are on different sheets and I needed to do a summary in the same book but I didn't want to load tables separately... now I'm going with this improvement. Thank you!!!
Amazing function. Not in use now very good to nknow
Thanks for this insight..
Thank you so much very cool tip
Super
Good. Thanks!
That’s pretty useful actually!
Would the expand button on the column header then expand and append all tables?
Awesome technique! I have a solution with appends that I definitely will be rewriting 😅
Brilliant :)
Hi 😊 you're doing great job 😊 is it possible to buy a course or something like this just to ask a few questions?
Quick question. Can we make the a tab reference in a power query dynamic?
In case of I have a table from query naming Query_Tab1 in excel file 1. Then I add the extra some column beside of Query_Tab1. I want to get the new data, including the Q_T1 and news extra column to a new table nam wing Tab2. Then combine Tab2 to my report.. Many thanks
Supercool!
Great video.
Btw, how do I load all tables (in one sheet or multiple sheets) in Excel into PQ in one swoop, without checking one by one in Navigator Select Multiple Items?
Nice to connect-need to know how can we minimize query refresh time-Source data same excel-4-5 tables
Nifty!
How would you now combine the tables? What would the next step look like
Very good - your videos are avid watching.
Could you do a video on automating sorting columns. So have multiple tables with month and year columns which after being appended produce one table but columns are not in month date order. How could you automate this?
This is good, however how to select just one of the available queries? I am sure you could filter for just one of the tables in the list. Some time ago, I found a way (worked with Ken Puls on this) to programatically select a query by its name: (this is in a function). Let me know what you think.
(qName) =>
let
qName2=Expression.Identifier(qName),
Source = Expression.Evaluate(qName2,#shared)
in
Source
Any idea how we can auto refresh dynamic query of API?
Thanks a lot. Is it possible to use recursion in power query, i.e. x(i)=a*x(i+1)+b where x(N) is given
Excellence
Thanks!
I found it very useful and I am really looking forword to learn more but not understand the video fully due to the English language could you please explain it in Marathi or Hindi.
www.youtube.com/@desigoodly
I think creating custom M function using one data source then putting all the files in folder and importing it PQ and invoke custom function on selected files may be alternative solution.
Where do you take all of these pearls? 🤔
Do you read manuals and helps? ☺
Amazing tip. I see ways how I can use #sections.
I’m not sure I understand how those tables are being appended to one another. Could you clarify?
Numeric distribution - How to create measure on multi level also if sum of qty is negative it will be ignore, i have the column like year, month, region, branch, store code, date, product code, distributor code, Sales qty and value, plz help
Nice Video Chandeep, Could you please slow down the pace of speaking..
You can adjust the playback speed in TH-cam to what suits you best. Click on the gear icon to get to the setting.
Dear Friend. I have one problem. I have this worksheet im developing for my Menu Costing. There's about 60 menus and it might keep expanding with time, and each menu i made on sheet of each with same template of sheet with one another. Each sheet have fixed position of data. Example: Menu Costing, Suggested selling price, current selling price, margin status bad or good. Then i want to make one Summary Sheet which i can view all these data from one master sheet. How can i draw data from this multiple sheet into one master sheet with the challenge the sheet might expand in the future and i want it to automatically insert data to the master list
Too bad I have an instance where I must merge because of the different columns needed between tables. Unfortunately, append does not help.
How to use function or query from another excel file? (not copy, just reference)
Hi sir This courses is live courses or recorded
recorded
You won't believe but I tried it in Power BI and I won't load in Data model.
I've used it Excel.
LITERAL HIGH WIZARD
Hi @Candeep,
I have loved your way of solving so may queries and your explanations.
Could you please help on the below Query. Actually the Query is written in Tableau. I need to wright the same logic in Power BI.
(in DAX or 'M' Language.) I have Struck in my work. please help me on the same @Candeep
If [row_number] = 0 then upper((trim( split (Trim( SPLIT( [COLUMN_NAME], "||",1)),":",1))))
else (trim( split (TRIM( SPLIT([COLUMN_NAME],"||",1)), ":", 2))) END
If [row_number] = 0 then upper((trim( split (Trim( SPLIT( [COLUMN_NAME], "||",2)),":",1))))
else (trim( split (TRIM( SPLIT([COLUMN_NAME],"||",2)), ":" ,2))) END
If [row_number] = 0 then upper((trim( split (Trim( SPLIT( [COLUMN_NAME], "||",3)),":",1))))
else (trim( split (TRIM( SPLIT([COLUMN_NAME],"||",3)) ,":", 2))) END
If [row_number] = 0 then upper((trim( split (Trim( SPLIT( [COLUMN_NAME], "||",4)),":",1))))
else (trim( split (TRIM( SPLIT([COLUMN_NAME],"||",4)), ":", 2))) END
If [row_number] = 0 then upper((trim( split (Trim( SPLIT( [COLUMN_NAME], "||",5)),":",1))))
else (trim( split (TRIM( SPLIT([COLUMN_NAME],"||",5)), ":", 2))) END
etc..................