Excellent content.. loved it.. A tip, the Excel.Workbook function has an additional parameter, by keying in "true" you can skip the promote headers step.
The "meta" trick is very useful in some situations, but it should come with a big warning that if you add steps to the end of the query containing the "meta" statement, you have to manually cut/paste that part to the end of whatever the new "last step" is. Otherwise, any queries that depend on that meta step will break. Unfortunately, it isn't one of those persistent things that automatically updates the M code when you add more steps 😒
That's the reason why I prefer the record-replacement trick instead of using hardcoded meta step-value . Just replace the LET and the IN part with square brackets and voilà... your query keeps functional and you can reference each step you want from outside.
@@adrianoschwenkberg6773 Ohhhhhh, that's the thing where you convert the whole query into one big record, right? I saw someone do that, and I was like, "Well, that's interesting, but I don't know what I would do with it." Thanks!!
I have a card in Power BI showing the last value of a column in a date format. How can I make the background colour change if the date displayed is older than two days from today. I have clicked the FX button next to the background colour in the card formatting section, but it only allows some conditional formatting with a text option, no formula option. Thanks
You have to develop a measure that checks the condition and returns the hex color code that you want. E.g. if condition met then "#4B55W4" (here you place the color code you want)
Im 50% sure, that you are able to do so. 1:32 instead of using Excel.Workbook, use CSV.Document. Im having the same issue and will try this tomorrow morning in the office.
nice workflow, thank you. may I give you an alternative way for this common typo-problem here ua-cam.com/video/BUazha3S6Ro/v-deo.htmlsi=mgUNjhxXRUT5fo7Z&t=129 After autocomplete the functionname by pressing tab-key, then hold shift-key + Down-Arrow. Now you can type the open-bracket and the whole expression becomes wrapped arround the brackets and you dont even need to close it. Second note, i prefer to type functionnames by starting with the period-key. For example .Combine instead of Table.Combine. In that case you have less typing and the autocomplete dont mess with double-ing the text TableTable.Combine.
Absolutely Outstanding. Concise and Crystal Clear. Truly Power Query Magic
Thanks for sharing your knowledge. Used the pattern extraction and others. Great stuff! Love the modem sound during the intro as well! 56K!
Glad it was helpful!
Great content... exactly what I needed to combine several sheets with different headers into one master sheet!
Excellent content.. loved it.. A tip, the Excel.Workbook function has an additional parameter, by keying in "true" you can skip the promote headers step.
The meta magic is incredible! Just what I have been looking for.
Thanks
That meta tip is gold. Thank you
Great as usual but I'll have to go through it a couple of times to work it out!
Man! You are a freak of PQ. Thanks dude.
Please UA-cam, Could I like this tutorial a million times?
Excellent as always👍. Thanks James❤
Great tips!
You're a Meta Wizard 🧙🏽 🤟🏽
The "meta" trick is very useful in some situations, but it should come with a big warning that if you add steps to the end of the query containing the "meta" statement, you have to manually cut/paste that part to the end of whatever the new "last step" is. Otherwise, any queries that depend on that meta step will break. Unfortunately, it isn't one of those persistent things that automatically updates the M code when you add more steps 😒
That's the reason why I prefer the record-replacement trick instead of using hardcoded meta step-value . Just replace the LET and the IN part with square brackets and voilà... your query keeps functional and you can reference each step you want from outside.
@@adrianoschwenkberg6773 Ohhhhhh, that's the thing where you convert the whole query into one big record, right? I saw someone do that, and I was like, "Well, that's interesting, but I don't know what I would do with it." Thanks!!
@larmondoflairallen4705 for example as a container for your personal custom function collection...
I have a card in Power BI showing the last value of a column in a date format. How can I make the background colour change if the date displayed is older than two days from today. I have clicked the FX button next to the background colour in the card formatting section, but it only allows some conditional formatting with a text option, no formula option. Thanks
You have to develop a measure that checks the condition and returns the hex color code that you want. E.g. if condition met then "#4B55W4" (here you place the color code you want)
Can I apply this to a folder containing CSV files so that I do not have to convert each of them to Excel?
Im 50% sure, that you are able to do so.
1:32 instead of using Excel.Workbook, use CSV.Document.
Im having the same issue and will try this tomorrow morning in the office.
nice workflow, thank you.
may I give you an alternative way for this common typo-problem here ua-cam.com/video/BUazha3S6Ro/v-deo.htmlsi=mgUNjhxXRUT5fo7Z&t=129
After autocomplete the functionname by pressing tab-key, then hold shift-key + Down-Arrow. Now you can type the open-bracket and the whole expression becomes wrapped arround the brackets and you dont even need to close it.
Second note, i prefer to type functionnames by starting with the period-key. For example .Combine instead of Table.Combine. In that case you have less typing and the autocomplete dont mess with double-ing the text TableTable.Combine.
That is a handy tip. It will be a grand and glorious day when Microsoft finally fixes that infuriating "feature" that everyone complains about.
Absolutely Outstanding. Concise and Crystal Clear. Truly Power Query Magic
Absolutely Outstanding. Concise and Crystal Clear. Truly Power Query Magic