Hm. I actually heard that the numbers I'm getting are great (given the channel is less than a year old). But maybe that's just people giving me "the glass is half full" version of the story 😂. But those words do mean a lot to me Jacek. A big wave to Poland from Slovenia.
Another brilliant one! Thanks, Gasper! Fuzzy match is an absolute beast in terms of joins! Actually, you can retrieve more than one column with xlookup though. You can retrieve a whole array instead of a single column, because of the dynamic arrays (if you got Excel 365 or Excel 2021). ;)
Thank you, never knew I could actually MERGE where there is a one to many, where with Xlookup we lookup something from a table where it is only once. And the Aggregate option makes this SO useful. Thanks from South Africa.
Thanks a lot again for this great video. Very helpful. I nearly prefer PQ every time. As a controller, I am so often confronted with Excel files and non transparent formulas that sooner or later can lead to many errors after updates and changes. Any PQ solution set up correctly is more robust and much more transparent.
Thanks, Dennis. I agree that a Power Query solution is way more systematic and bulletproof. IT does, however, require some knowledge. For instance, if you expand a Merge, you could get extra rows of data and other similar examples.
@@ExcelOlympics Thanks a lot. Maybe as a suggestion for a PQ future video. I would be interested in a way to get a Running Total (YTD) by subcategory with a different fiscal year (e.g. start October) from monthly values. That's where I'm stuck with a problem right now as a PQ newbie. That would be so cool. Thanks a lot in advance!
Hi Dennis. Any particular reason for doing this in Power Query as Power Pivot is built for this as DATESYTD has an optional argument for custom year end?
Thanks a lot for your help. I will try my best with you advice! Yes, I know the YTD formula in power pivot but I have to do it in pc because of further transformations and joins with other tables. I feel more comfortable doing it in pq.
Thanks Gasper. Great comparison! I like to try and solve first with formulas, but when they won't do, Power Query comes to the rescue! Thanks for sharing and thumbs up!!
I was trying to do a merge between two queries in Power Query using the left outer joint. But the second query is against a column that is a merge column of 5 text columns. So, I did not find a solution in Power Query and I ended using Xlookup. The formula used was =XLOOKUP("*"&E5&"*", code,quantity, "no match", 2), where a lookup-value, as text, can match a specific text contained in the merged column with different texts separated by a space. If there is a way to do it in Power, let me know. Thanks
It's so sad that your videos with such content have only hundreds of views, you are doing great job. Greetings from Poland.
Hm. I actually heard that the numbers I'm getting are great (given the channel is less than a year old). But maybe that's just people giving me "the glass is half full" version of the story 😂. But those words do mean a lot to me Jacek. A big wave to Poland from Slovenia.
Another brilliant one! Thanks, Gasper! Fuzzy match is an absolute beast in terms of joins!
Actually, you can retrieve more than one column with xlookup though. You can retrieve a whole array instead of a single column, because of the dynamic arrays (if you got Excel 365 or Excel 2021).
;)
True Stefan. Thanks for adding that.
Thank you, never knew I could actually MERGE where there is a one to many, where with Xlookup we lookup something from a table where it is only once. And the Aggregate option makes this SO useful. Thanks from South Africa.
You are welcome from Slovenia 😃
Thanks a lot again for this great video. Very helpful. I nearly prefer PQ every time. As a controller, I am so often confronted with Excel files and non transparent formulas that sooner or later can lead to many errors after updates and changes. Any PQ solution set up correctly is more robust and much more transparent.
Thanks, Dennis. I agree that a Power Query solution is way more systematic and bulletproof. IT does, however, require some knowledge. For instance, if you expand a Merge, you could get extra rows of data and other similar examples.
@@ExcelOlympics Thanks a lot. Maybe as a suggestion for a PQ future video. I would be interested in a way to get a Running Total (YTD) by subcategory with a different fiscal year (e.g. start October) from monthly values. That's where I'm stuck with a problem right now as a PQ newbie. That would be so cool. Thanks a lot in advance!
@@DK_85 Hi Dennis, do some searches for list.accumulate in power query. I believe this is the function you need!!
Hi Dennis. Any particular reason for doing this in Power Query as Power Pivot is built for this as DATESYTD has an optional argument for custom year end?
Thanks a lot for your help. I will try my best with you advice! Yes, I know the YTD formula in power pivot but I have to do it in pc because of further transformations and joins with other tables. I feel more comfortable doing it in pq.
Thanks Gasper. Great comparison! I like to try and solve first with formulas, but when they won't do, Power Query comes to the rescue! Thanks for sharing and thumbs up!!
Thanks Wayne. Power Query 911 line must busy all the time .)
This is great - can’t wait for next videos on the different joins👍
Thank you for your kind words Irene. The video should drop mid-February. See you there 😀.
A super snippet of knowledge
Thanks Pierre.
I would not say brillant, but superb 👌👌👍👍👏👏👏👏👏👏
I was trying to do a merge between two queries in Power Query using the left outer joint. But the second query is against a column that is a merge column of 5 text columns. So, I did not find a solution in Power Query and I ended using Xlookup. The formula used was =XLOOKUP("*"&E5&"*", code,quantity, "no match", 2), where a lookup-value, as text, can match a specific text contained in the merged column with different texts separated by a space. If there is a way to do it in Power, let me know. Thanks
Why does 1st row (with graficsl dont shows up?
Why have to doplicate tbl Instead of combining it with other tabl2??
Great Video Gasper
Thanks Martin. Means a lot!