cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
BishwaR
Helper III
Helper III

Distinct values in the column

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          |

3 ACCEPTED SOLUTIONS
edhans
Super User III
Super User III

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:

edhans_0-1607733874881.png

into this:

edhans_1-1607733902804.png

 

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.

 



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

View solution in original post

@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

View solution in original post

edhans
Super User III
Super User III

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



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

View solution in original post

6 REPLIES 6
edhans
Super User III
Super User III

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



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

View solution in original post

@edhans  Thank you so much for the additional info. I marked it as the solution

edhans
Super User III
Super User III

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:

edhans_0-1607733874881.png

into this:

edhans_1-1607733902804.png

 

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.

 



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

View solution in original post

@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

View solution in original post

edhans
Super User III
Super User III

 = 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.



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

Under 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

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are You Ready?

Test your skills now with the Cloud Skills Challenge.

Top Solution Authors