In the last scenario, Dynamically Expand Columns, I add a new column to the inner table with the content of the column of the outer table I want to keep. So I can use Table.Combine instead of Expand.
is there a way to show that the query created in power query hasn't been modified so that auditors can see that once the source file has been updated, the query will run in the same way ? i can't find anything; in Alteryx, you can see when the workflow was last modified but i don't know how to see this in power query so that's not good
@@BIGorilla hmmm, not sure they'll want to compare lines and lines of coding from one query to the next; some of my processes have way over 50 lines. I don't know why MS haven't thought of this as this is a basic requirement really; just like Alteryx offers; anyway, thanks for your help
@@pascaljoly5752 in all my years of working I’ve never heard of this requirement before. It sounds pretty niche to me. Power query started as basic data transformation tool in excel. Then later on it matured to power bi. And it grew. Alteryx is a different product, with licensing costs that are enormous. Not for the average self service employee. I think they cater a different profile user
Hi.... One request. Suppose we complie a data in power Query & now want to make filter range in Excel so that I could see only specific period or number range... Like 645070 to 645090 or SEP to NOV
When you add values to the Table Object (like the Table.AddIndexColumn does in the video) the table is not the same as the source data. It allows you to perform operations on the grouped rows -> then expand them without any data type issues.
How about that when it comes to Expand solution? Too simple? = Table.ExpandTableColumn(#"Grouped Rows", "Details", List.Difference(Table.ColumnNames(#"Grouped Rows"[Details]{0}), Table.ColumnNames(#"Grouped Rows")))
Hey mrBartus. That approach is very similar, with the difference being you used List.Difference, while I used List.RemoveItems. Other than that I showed it using variables, and you pasted it all together. Both methods work fine :)
Great. I did know that the table schema was optional. This solve my problem! Thanks!
Awesome! My favorite channel about M Code :)
Always a pleasure with such an appreciative audience. Thanks Rai!
Just received your book, Definitive guide to power query in the mail. Can't wait to get into it
You're the best. Hope you like it!
Very useful tips, thanks a lot Rick
You're so welcome!
Amazing! Thank you!
Wow! Je komt de laatste tijd wel met echt goede ideeën Rick! Deze video geeft weer veel inzicht in PQ Dankjewel
Woho, dank voor je compliment. Hoop dat je er iet smee kunt!
🤯 thanks for sharing this!
hope you like it!
Just amazing! Thanks
Always a pleasure :)
Wow, nice tricks
💚
There is a lot of gold in that book.
🎉
Cheers!
In the last scenario, Dynamically Expand Columns, I add a new column to the inner table with the content of the column of the outer table I want to keep. So I can use Table.Combine instead of Expand.
That also works, absolutely!
Hi Rick,
If I buy the paper book version, will I get PDF version aswell?
Hey there - absolutely. When you get the print version, you can request the PDF for free with Packt. Instructions are in the book :)
@@BIGorilla Indeed with the original code inserte in the first page of the pdf .. and how useful it is !!! Thanks Rick !!
is there a way to show that the query created in power query hasn't been modified so that auditors can see that once the source file has been updated, the query will run in the same way ? i can't find anything; in Alteryx, you can see when the workflow was last modified but i don't know how to see this in power query so that's not good
I don’t think so. But you could share the queries with them, so they can verify whether these are the same still. That should suffice?
@@BIGorilla hmmm, not sure they'll want to compare lines and lines of coding from one query to the next; some of my processes have way over 50 lines. I don't know why MS haven't thought of this as this is a basic requirement really; just like Alteryx offers; anyway, thanks for your help
@@pascaljoly5752 in all my years of working I’ve never heard of this requirement before. It sounds pretty niche to me.
Power query started as basic data transformation tool in excel. Then later on it matured to power bi. And it grew.
Alteryx is a different product, with licensing costs that are enormous. Not for the average self service employee. I think they cater a different profile user
Hi.... One request. Suppose we complie a data in power Query & now want to make filter range in Excel so that I could see only specific period or number range... Like 645070 to 645090 or SEP to NOV
for 1 option: Combining table -> if we drill down detail column then expand => It the same with source data, so what is meaning here?
When you add values to the Table Object (like the Table.AddIndexColumn does in the video) the table is not the same as the source data. It allows you to perform operations on the grouped rows -> then expand them without any data type issues.
Could you please share the file so we could follow along?
hey, the file is there on his written article on description
@@amitpatel75 Great! Thank you!
How about that when it comes to Expand solution? Too simple?
= Table.ExpandTableColumn(#"Grouped Rows", "Details", List.Difference(Table.ColumnNames(#"Grouped Rows"[Details]{0}), Table.ColumnNames(#"Grouped Rows")))
Hey mrBartus. That approach is very similar, with the difference being you used List.Difference, while I used List.RemoveItems. Other than that I showed it using variables, and you pasted it all together.
Both methods work fine :)
@@BIGorilla Thank you for the explanation :)