I love that Patrick didn't know you could "click on the gray space" in Power Query to see a preview of the data/error etc. It's just proof that none of us can possibly know everything there is about Power BI and Power Query. I've been doing Power BI since before it was even a product, and there's still things I learn. It's refreshing to see we're all still learning and trying figure things out. I also really loved the [dataColumn] and {[column1],[column2]} list of columns after the query or query step part. I had no idea that's how that worked. So useful from Alex!
Start with the simple stuff - primitive and structured values (table, list, record) and go from there! Trust me - I still fall back on the basics of data types to get me thru these brain teasers.
Hey, nice work. But I have a question: How does the slicer selection (from Power BI) communicate with query's selected rows (from Power Query)?. That part is still unclear for me. Thank!!
Could this method be used to break apart tables into separate queries? When I connect to a PostgreSQL data source it returns me a list of tables in the Dataflow. Currently I have to go table by table and add as new query then rename it with it's table name.
Hi Patrick is there any way to copy the multiples data from excel and paste in the slicer search bar and the value get selected. Just like copy and paste
Unfortunately not today, but this is something we've discussed with the team and would love to see - I'd encourage you to vote over on Ideas.PowerBI.com if you haven't already on this item: ideas.powerbi.com/ideas/idea/?ideaid=a534ed70-4d11-4815-862a-11c8440ab567
@@ItsNotAboutTheCell thanks team for the response, Me and my team will vote to this idea. I am facing issue with the values having single quotes (') in between such as: D'AGNESE I have followed the dynamic parameter document. It is working fine for value that don't have the single quotes. Can u suggest something on this
Hi Ptrick, I am facing issue with single quotes that is coming in between the names. I have used dynamic parameter concepts and Bind parameter with a field. It is working fine with the values having no single quotes. But giving me error with single quotes. Can you please help me on this?
So these 4 lines: ParamType = Value.Type(PromoParam), ParamTypeIsList = Type.Is(ParamType, List.Type), queryParam = if ParamTypeIsList then Could be 2 lines: queryParam = if ProtoParam is list then And these 16 lines: List.Transform( PromoParam, each [ Selection = _, Data = Table.SelectRows( dbo_DimPromotion, each Text.Contains( [PromotionDescription], Selection ) ) ][Data] ) Could be 12 lines: List.Transform( PromoParam, (EachListItem) => Table.SelectRows( dbo_DimPromotion, each Text.Contains( [PromotionDescription], EachListItem ) ) ) Also: { } and [ ] ALWAYS act as either a CONSTRUCTOR or SELECTOR [ ] to select/construct NAMED things { } to select/construct NUMBERED things CONSTRUCTOR: {1, 2, 3} = {1..3} = List = 1d Array of values, each value mapped to an index NUMBER [a=1, b=2, c=3] = Record = 1d Array of values, each value mapped to a field NAME SELECTOR: Table[ColumnName] = List ▯▮▯ ▯▮▯ ▯▮▯ Table{RowNumber} = Record ▯▯▯ ▮▮▮ ▯▯▯ Table[ColumnName]{RowNumber} = Value ▯▯▯ ▯▮▯ ▯▯▯ Record[FieldName] = Value ▯ ▮ ▯ List{IndexNumber} = Value ▯ ▮ ▯ Table[[Column1], [Column2]] = Table ▯▮▮▯ ▯▮▮▯ ▯▮▮▯ Table{[Column1="x", Column2="y"]} = Record ▯▯▯ ▮▮▮ ▯▯▯
@@ItsNotAboutTheCell Appreciate that! One last idea. Sorry, I get excited about making M as compact and easy to read as possible, it's such a good language! Could it be more performant to invert the nested loops? (aka each inside each, aka Table.SelectRows inside List.Transform) Could look something like this, 4 steps, a bit nested, but rather readable? let Source = Sql.Database( ServerAddress, "ContosoRetailDW" ), dbo_DimPromotion = Source{[ Schema = "dbo", Item = "DimPromotion" ]} [Data], ParamList = if PromoParam is list then PromoParam else {PromoParam}, #"Filtered Rows" = Table.SelectRows( dbo_DimPromotion, each List.AnyTrue( List.Transform( ParamList, (EachParamItem) => Text.Contains( [PromotionDescription], EachParamItem, Comparer.OrdinalIgnoreCase ) ) ) ) in #"Filtered Rows"
@@benrogers9092 - "This step results in a query that is not supported in DirectQuery mode. Switch all tables to import mode." We only had one rule... Don't break the fold! lol - not sure if you have the ContosoRetailDW database loaded but here's a download link that I used - www.microsoft.com/en-ca/download/details.aspx?id=18279 And I agree, I know I'm casually getting Patrick in Power Query and M - (his first love is always Modeling/DAX) - but this experience has shown me that people are starting to see the deep end and wondering how they can swim out to get to this level. Will be fun to think about a building blocks series if GIAC is up for it.
@@ItsNotAboutTheCell Ah, good catch about the folding! My error. Yes, I would love to see more M content from GIAC, I feel like quality information for this language is difficult to find.
Hi, Please help me I am new in power bi and I need to import data from multi sources up to 40 billion rows. How can I handle large data set with out performance issues. Please suggest me. Thanks in advance.
First thing is to follow best practices! A clean star schema should be used - docs.microsoft.com/en-us/power-bi/guidance/star-schema. We also have a lot of videos about features that help you scale like Aggregate tables, partitioning, etc...
Hello guy, I had a requirement. I had a issue reported date column where I need to use this column to add x-axis constant line. But i can add only either earliest or latest date. But i need to show all reported dates in x-axis as constant lines. How can I show all multiple constant lines? Is it possible.
@@ItsNotAboutTheCell so I have this mapping table.. for example: table1 || key1 || table2 || key2 In this mapping..values of column table1 remains unchanged.. and from column key1 to column key2.. gets changed after the completion of the nested join.. like this.. Table.NestedJoin(table1, key1, table2, key2, "newname", JoinKind.LeftOuter).. and then goes to row 2.. changes everything from key1 to key2, except table1. Like an iteration.
Hello guys is there any dax formula or function in Power BI that can display the max value of for instance work hours at any point in time to display what project a worker has delegated his most time to? I know I can see it on a visualization but I am trying to make a custom column that is interactive with time. Is this even possible or should I try and export my data model to something like SQL where I can at least track changes in project hours over a certain period of time.
Power Query and M is amazing, but the service doesn't always honor all features. For example if you dynamically add columns or use pivot the service will not add or remove columns as the data changes.
Yeah, Power BI datasets won't preserve dynamic schema changes to avoid potentially introducing unexpected changes. I'd be curious under what scenarios are you needing this type of experience, as I've heard others make such a request too.
@@ItsNotAboutTheCellLong answer: My team has buit an automated tool that transforms survey results into a data model. It uploads a dataset and connects it to a gateway. Survey data is tricky to model. To properly build a model that allows cross filtering across questions the user of the model needs a dimension with attributes. The problem is each survey is unique and requires a dynamic dimension. We have custom M code to build this dimension based on settings in our app. However, the service won't honor the code and requires the user to build the dataset on their desktop each time the settings are changed then upload the new file vs using our app to refresh the dataset using the service api.
Amazing Video. I am evolving as a huge M fan every day now. It is truly amazing. I have a question for you guys. How can I start with the query challenge? I will follow the link in the description but I would love to have more information about it.
@@ItsNotAboutTheCell thanks for the response. I have a background in R and some Python, but due to performance issues I went full M to develop solutions within power query all the way. Do you have any resource to recommend to go deep in M? I will go through your channel as well.
@@mabenba for me I went thru the Power Query M language specifications guide back when it was PDF (and before we had any form of Intellisense!! so it was really fight or flight back then) - docs.microsoft.com/en-us/powerquery-m/power-query-m-language-specification - while it can be dry material, it's incredibly helpful to solidify the building blocks. Also, the work that Ben has put into the Power Query primer series is another master class for diving into the deep end - bengribaudo.com/blog/2017/11/17/4107/power-query-m-primer-part1-introduction-simple-expressions-let And I'll be perfectly honest, my journey mirrors a lot of others in starting with M is for Data Monkeys and then wrestling the worst formatted Excel spreadsheets in my day job to make the puzzle pieces fit into the right tabular shape needed for analytics. There are still new challenges I tackle all the time but I always fall back on the fundamentals (from the spec guide) to get me through to the final solution.
@@ItsNotAboutTheCell, thanks for your response. I really appretiate your time. Wrestling with the obscure excel files the clients send me is the way I am learning, and how I went from the new guy to the M guru where I am working. I receive like 10 reports in excel and PDF to transform everyday into a power bi report and let my tell you that they seem to be made ultra complex on purpouse just to mess with me. I tend to go now and then to the docs. thanks again for your time.
nice .... for me it was too fast you Explanation. And to much zooming. little Bit slower and zoom less. In this case I dont understud the doing. there are a discription or a how 2 Video ?
Hey Barriga - sounds like a blog post is in order for those who really want to dig in to the code more. I know in the spirit of video length we kept it short to display possibilities and left the code sample for people to try out but I’ll whip up an article and will keep you posted on its release.
Hey Sham - I was thinking about expanding upon this for the Power Query blog, it's a lot to digest and write while still attempting to keep a video short, which is why I opted out of doing too much live code in the video. Please let me know if that's something that you'd be interested in reading for this topic.
@@ItsNotAboutTheCell absolutely, would love to go through it in detail and connect on further questions, which I am sure I will be having, looking forward to it ❤️
I love that Patrick didn't know you could "click on the gray space" in Power Query to see a preview of the data/error etc. It's just proof that none of us can possibly know everything there is about Power BI and Power Query. I've been doing Power BI since before it was even a product, and there's still things I learn. It's refreshing to see we're all still learning and trying figure things out.
I also really loved the [dataColumn] and {[column1],[column2]} list of columns after the query or query step part. I had no idea that's how that worked.
So useful from Alex!
WOW! I still have a lot to learn!
Start with the simple stuff - primitive and structured values (table, list, record) and go from there! Trust me - I still fall back on the basics of data types to get me thru these brain teasers.
This hits. Lol
Hey, nice work. But I have a question: How does the slicer selection (from Power BI) communicate with query's selected rows (from Power Query)?. That part is still unclear for me. Thank!!
#PowerQueryEverything!!!
#PowerQueryEverything!!!
Wait a minute, you can get power query to do stuff from the power Bi front end? I thought it was a one way linear process? Cool!
Dynamic M parameters when using DirectQuery. It only works with certain data sources though.
top shelf stuff fellas! thanks!
Could this method be used to break apart tables into separate queries? When I connect to a PostgreSQL data source it returns me a list of tables in the Dataflow. Currently I have to go table by table and add as new query then rename it with it's table name.
Unfortunately nothing like a bulk creation or new tables exist. But definitely a great idea I can pass along to the team.
Hi Patrick is there any way to copy the multiples data from excel and paste in the slicer search bar and the value get selected. Just like copy and paste
Unfortunately not today, but this is something we've discussed with the team and would love to see - I'd encourage you to vote over on Ideas.PowerBI.com if you haven't already on this item: ideas.powerbi.com/ideas/idea/?ideaid=a534ed70-4d11-4815-862a-11c8440ab567
@@ItsNotAboutTheCell thanks team for the response, Me and my team will vote to this idea.
I am facing issue with the values having single quotes (') in between such as: D'AGNESE
I have followed the dynamic parameter document. It is working fine for value that don't have the single quotes.
Can u suggest something on this
Love that formatted M code!
Formatted M is just too much 💛💛💛
Hi Ptrick,
I am facing issue with single quotes that is coming in between the names.
I have used dynamic parameter concepts and Bind parameter with a field.
It is working fine with the values having no single quotes.
But giving me error with single quotes.
Can you please help me on this?
Can you suggest something on the issue
So these 4 lines:
ParamType = Value.Type(PromoParam),
ParamTypeIsList = Type.Is(ParamType, List.Type),
queryParam =
if ParamTypeIsList then
Could be 2 lines:
queryParam =
if ProtoParam is list then
And these 16 lines:
List.Transform(
PromoParam,
each
[
Selection = _,
Data =
Table.SelectRows(
dbo_DimPromotion,
each
Text.Contains(
[PromotionDescription],
Selection
)
)
][Data]
)
Could be 12 lines:
List.Transform(
PromoParam,
(EachListItem) =>
Table.SelectRows(
dbo_DimPromotion,
each
Text.Contains(
[PromotionDescription],
EachListItem
)
)
)
Also:
{ } and [ ] ALWAYS act as either a CONSTRUCTOR or SELECTOR
[ ] to select/construct NAMED things
{ } to select/construct NUMBERED things
CONSTRUCTOR:
{1, 2, 3} = {1..3} = List = 1d Array of values, each value mapped to an index NUMBER
[a=1, b=2, c=3] = Record = 1d Array of values, each value mapped to a field NAME
SELECTOR:
Table[ColumnName] = List
▯▮▯
▯▮▯
▯▮▯
Table{RowNumber} = Record
▯▯▯
▮▮▮
▯▯▯
Table[ColumnName]{RowNumber} = Value
▯▯▯
▯▮▯
▯▯▯
Record[FieldName] = Value
▯
▮
▯
List{IndexNumber} = Value
▯
▮
▯
Table[[Column1], [Column2]] = Table
▯▮▮▯
▯▮▮▯
▯▮▮▯
Table{[Column1="x", Column2="y"]} = Record
▯▯▯
▮▮▮
▯▯▯
I always love seeing how others tackle similar problems, great write up and solutioning Ben!
@@ItsNotAboutTheCell Appreciate that! One last idea. Sorry, I get excited about making M as compact and easy to read as possible, it's such a good language!
Could it be more performant to invert the nested loops? (aka each inside each, aka Table.SelectRows inside List.Transform) Could look something like this, 4 steps, a bit nested, but rather readable?
let
Source =
Sql.Database(
ServerAddress,
"ContosoRetailDW"
),
dbo_DimPromotion =
Source{[
Schema = "dbo",
Item = "DimPromotion"
]}
[Data],
ParamList = if PromoParam is list then PromoParam else {PromoParam},
#"Filtered Rows" =
Table.SelectRows(
dbo_DimPromotion,
each List.AnyTrue(
List.Transform(
ParamList,
(EachParamItem) => Text.Contains(
[PromotionDescription],
EachParamItem,
Comparer.OrdinalIgnoreCase
)
)
)
)
in
#"Filtered Rows"
@@benrogers9092 - "This step results in a query that is not supported in DirectQuery mode. Switch all tables to import mode."
We only had one rule... Don't break the fold! lol - not sure if you have the ContosoRetailDW database loaded but here's a download link that I used - www.microsoft.com/en-ca/download/details.aspx?id=18279
And I agree, I know I'm casually getting Patrick in Power Query and M - (his first love is always Modeling/DAX) - but this experience has shown me that people are starting to see the deep end and wondering how they can swim out to get to this level.
Will be fun to think about a building blocks series if GIAC is up for it.
@@ItsNotAboutTheCell Ah, good catch about the folding! My error. Yes, I would love to see more M content from GIAC, I feel like quality information for this language is difficult to find.
Hi, Please help me I am new in power bi and I need to import data from multi sources up to 40 billion rows. How can I handle large data set with out performance issues. Please suggest me. Thanks in advance.
First thing is to follow best practices! A clean star schema should be used - docs.microsoft.com/en-us/power-bi/guidance/star-schema. We also have a lot of videos about features that help you scale like Aggregate tables, partitioning, etc...
Hello guy, I had a requirement. I had a issue reported date column where I need to use this column to add x-axis constant line. But i can add only either earliest or latest date. But i need to show all reported dates in x-axis as constant lines. How can I show all multiple constant lines? Is it possible.
How to loop over a list in nested join?
Hey Ashish - do you happen to have an example of the data? I'd be happy to take a look at your scenario and provide some thoughts.
@@ItsNotAboutTheCell so I have this mapping table.. for example:
table1 || key1 || table2 || key2
In this mapping..values of column table1 remains unchanged.. and from column key1 to column key2.. gets changed after the completion of the nested join.. like this.. Table.NestedJoin(table1, key1, table2, key2, "newname", JoinKind.LeftOuter).. and then goes to row 2.. changes everything from key1 to key2, except table1. Like an iteration.
Hello guys is there any dax formula or function in Power BI that can display the max value of for instance work hours at any point in time to display what project a worker has delegated his most time to? I know I can see it on a visualization but I am trying to make a custom column that is interactive with time. Is this even possible or should I try and export my data model to something like SQL where I can at least track changes in project hours over a certain period of time.
Use Summarize and get context table then further filter the table on project column.
Power Query and M is amazing, but the service doesn't always honor all features. For example if you dynamically add columns or use pivot the service will not add or remove columns as the data changes.
Yeah, Power BI datasets won't preserve dynamic schema changes to avoid potentially introducing unexpected changes. I'd be curious under what scenarios are you needing this type of experience, as I've heard others make such a request too.
@@ItsNotAboutTheCellLong answer: My team has buit an automated tool that transforms survey results into a data model. It uploads a dataset and connects it to a gateway. Survey data is tricky to model. To properly build a model that allows cross filtering across questions the user of the model needs a dimension with attributes. The problem is each survey is unique and requires a dynamic dimension. We have custom M code to build this dimension based on settings in our app. However, the service won't honor the code and requires the user to build the dataset on their desktop each time the settings are changed then upload the new file vs using our app to refresh the dataset using the service api.
Good job
Why do with M and not create a DAX table using ALLSELECTED?
This is a specific scenario using Dynamic M Parameters in DirectQuery mode.
Amazing Video. I am evolving as a huge M fan every day now. It is truly amazing. I have a question for you guys. How can I start with the query challenge? I will follow the link in the description but I would love to have more information about it.
Here’s a link to the playlist and I go over the challenge in the intro video - ua-cam.com/play/PLKW7XPyNDgRCorKNS1bfZoAO3YSIAVz3N.html
@@ItsNotAboutTheCell thanks for the response. I have a background in R and some Python, but due to performance issues I went full M to develop solutions within power query all the way. Do you have any resource to recommend to go deep in M? I will go through your channel as well.
@@mabenba for me I went thru the Power Query M language specifications guide back when it was PDF (and before we had any form of Intellisense!! so it was really fight or flight back then) - docs.microsoft.com/en-us/powerquery-m/power-query-m-language-specification - while it can be dry material, it's incredibly helpful to solidify the building blocks. Also, the work that Ben has put into the Power Query primer series is another master class for diving into the deep end - bengribaudo.com/blog/2017/11/17/4107/power-query-m-primer-part1-introduction-simple-expressions-let
And I'll be perfectly honest, my journey mirrors a lot of others in starting with M is for Data Monkeys and then wrestling the worst formatted Excel spreadsheets in my day job to make the puzzle pieces fit into the right tabular shape needed for analytics. There are still new challenges I tackle all the time but I always fall back on the fundamentals (from the spec guide) to get me through to the final solution.
@@ItsNotAboutTheCell, thanks for your response. I really appretiate your time. Wrestling with the obscure excel files the clients send me is the way I am learning, and how I went from the new guy to the M guru where I am working. I receive like 10 reports in excel and PDF to transform everyday into a power bi report and let my tell you that they seem to be made ultra complex on purpouse just to mess with me.
I tend to go now and then to the docs.
thanks again for your time.
#PowerQuery magic!
I want a Sisser tee
I'll let you know when the monster truck t-shirts hit the stock again, I think they have a new order coming up :) - sisser.bandcamp.com/merch
Damn
It's "M"-agic what you can do with this language!
nice ....
for me it was too fast you Explanation. And to much zooming. little Bit slower and zoom less.
In this case I dont understud the doing.
there are a discription or a how 2 Video ?
Hey Barriga - sounds like a blog post is in order for those who really want to dig in to the code more. I know in the spirit of video length we kept it short to display possibilities and left the code sample for people to try out but I’ll whip up an article and will keep you posted on its release.
@@ItsNotAboutTheCell Thank you for this great video. As Barriga, I would love to have an article with code sample on this M language "trick" :)
@@anisnaim4947 will do! Hoping folks enjoyed the recent video where we did UI only :)
Couldn't follow everything 😒 was bit fast for me
Hey Sham - I was thinking about expanding upon this for the Power Query blog, it's a lot to digest and write while still attempting to keep a video short, which is why I opted out of doing too much live code in the video. Please let me know if that's something that you'd be interested in reading for this topic.
@@ItsNotAboutTheCell absolutely, would love to go through it in detail and connect on further questions, which I am sure I will be having, looking forward to it ❤️