Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
I have a product table with redundant product names. I have to create a column in the same table next to Product column with the distict Product Name. How to write a dax for it ?
Product| Distinct Name
A | A
A |
A |
B | B
B |
Thanks
B |
Solved! Go to Solution.
Hi @BishwaR
I can do this in Power Query. In PQ, I can manipulate lists to build a table that has the normal values (product) in one column, then just the distinct value in another, then combine the unequal length lists, leaving null where there is no data.
I'm not sure how to do this in DAX. There is no concept of "row above/below" to work with like this. There isn't in Power Query tables either. That is why I had to convert the column to a list and create unequal lengths that I put back together. Perhaps someone more clever with DAX can think of a way to not repeat data. I could do it if you had an index or some other field to work off of, but that requires Power Query too to get the index, and I figured, I'd just do it all in Power Query. This should be very performant even over large data sets. Lists are fast. And this is much better than a calculated column in terms of performance.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclSK1UElnWBkLAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Product = _t]),
#"Grouped Rows" =
Table.Group(
Source,
{"Product"},
{
{
"New Lists",
each
Table.FromColumns(
{
_[Product],
List.Distinct(_[Product])
},
Table.ColumnNames(Source) & {"Distinct"}
),
type table [Product = text, Distinct = text]
}
}
),
#"Removed Other Columns" = Table.SelectColumns(#"Grouped Rows",{"New Lists"}),
#"Expanded New Lists" = Table.ExpandTableColumn(#"Removed Other Columns", "New Lists", {"Product", "Distinct"}, {"Product", "Distinct"})
in
#"Expanded New Lists"
You don't have to know how it works unless you want me to walk you through it. I'll be happy to, but it turns this:
It turns this:
into this:
You will want to read the steps below to connect this to your data.
How to use M code provided in a blank query:
1) In Power Query, select New Source, then Blank Query
2) On the Home ribbon, select "Advanced Editor" button
3) Remove everything you see, then paste the M code I've given you in that box.
4) Press Done
5) See this article if you need help using this M code in your model.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI Reporting@edhans This is awesome. I will study it and try to digest. If I stumbled I will ask for more help in understanding the code. In the meantime I would ask you this. I want to improve my skill in M query please guide me how can I improve it. Any books or videos or traning courses ? Thanks a ton
Hi @BishwaR - glad to help. I hope you don't mind but I marked my answer as the solution. You, I think, inadvertently marked your response as the solution. Helps others find the actual answer.
As to classes/resources, it depends. If you want a book or online course, consider this
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingHi @BishwaR - glad to help. I hope you don't mind but I marked my answer as the solution. You, I think, inadvertently marked your response as the solution. Helps others find the actual answer.
As to classes/resources, it depends. If you want a book or online course, consider this
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingHi @BishwaR
I can do this in Power Query. In PQ, I can manipulate lists to build a table that has the normal values (product) in one column, then just the distinct value in another, then combine the unequal length lists, leaving null where there is no data.
I'm not sure how to do this in DAX. There is no concept of "row above/below" to work with like this. There isn't in Power Query tables either. That is why I had to convert the column to a list and create unequal lengths that I put back together. Perhaps someone more clever with DAX can think of a way to not repeat data. I could do it if you had an index or some other field to work off of, but that requires Power Query too to get the index, and I figured, I'd just do it all in Power Query. This should be very performant even over large data sets. Lists are fast. And this is much better than a calculated column in terms of performance.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclSK1UElnWBkLAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Product = _t]),
#"Grouped Rows" =
Table.Group(
Source,
{"Product"},
{
{
"New Lists",
each
Table.FromColumns(
{
_[Product],
List.Distinct(_[Product])
},
Table.ColumnNames(Source) & {"Distinct"}
),
type table [Product = text, Distinct = text]
}
}
),
#"Removed Other Columns" = Table.SelectColumns(#"Grouped Rows",{"New Lists"}),
#"Expanded New Lists" = Table.ExpandTableColumn(#"Removed Other Columns", "New Lists", {"Product", "Distinct"}, {"Product", "Distinct"})
in
#"Expanded New Lists"
You don't have to know how it works unless you want me to walk you through it. I'll be happy to, but it turns this:
It turns this:
into this:
You will want to read the steps below to connect this to your data.
How to use M code provided in a blank query:
1) In Power Query, select New Source, then Blank Query
2) On the Home ribbon, select "Advanced Editor" button
3) Remove everything you see, then paste the M code I've given you in that box.
4) Press Done
5) See this article if you need help using this M code in your model.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI Reporting@edhans This is awesome. I will study it and try to digest. If I stumbled I will ask for more help in understanding the code. In the meantime I would ask you this. I want to improve my skill in M query please guide me how can I improve it. Any books or videos or traning courses ? Thanks a ton
= table[Product]
just enter that in a calculated column.
I am not sure what you mean though by enter "Distinct product name". Your table has 5 rows with 2 values - A, B. A table with distinct products would have 2 rows. Not 5. You cannot add a 2 row column next to a 5 row column in the same table. All columns have the same number of rows.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingUnder the Column "Product" there are three As and two Bs and what I want in the next column is just to show a single value A instead of three As and a single B instead of two Bs
Thanks you for trying to help me