Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
Anonymous
Not applicable

Get the common values of all columns

Hi all, 

I am new to Power Bi and I have a data with a so many columns and with a target column ( values are 0 or 1 ).

I want to select the common values between the rows that have 0 as target and the rows that have 1 as target without using any measure.

For example : 

if we have this input :

 

Column AColumn BColumn CColumn DColumn ETarget
50604030220
60332030220
503310033221
22445333333001
60022220333001

 

Thou output should return all the columns that have a common values between the Target 0 and target 1

Column AColumn BColumn CColumn DColumn ETarget
5033--22-
--20---

 

I just want to highlight the common values that exists in my data between my targets without using any measure. If there is no common value i want to show that also.

 

Can anyone help me with this ? 

Thank you.

11 REPLIES 11
Anonymous
Not applicable

Hi @MFelix ,

Thank you so much for your very clear code and example , but it seems that i am facing other issue when i run the step of grouped rows : 

bm1994_0-1638873230973.png

even that field is setted to the data type : whole number. 

Do you have any idea how we can go around this ? or maybe just exclude this feature from our query ? 

 

Thank you.

Hi @Anonymous,

This has to do with the limit of decimal values that then is converted to scientific. Try to make it has text


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



Anonymous
Not applicable

Hi all, 

Does anyone have an idea ? 

 

Thank you.

Anonymous
Not applicable

Hi @MFelix ,

Thank you for your reply, Since that didn't work eaither so i dropped the feature for now ( it is not that important). 
after running your script i am only getting one row in the new table which is not the case, i want to return all the commun value not for all the columns only but between any column. 

In your opinion Is it just easier to work this on python script than just import the needed data into power bi ? ( talking from time consuming perspective ).

Or maybe we just create each time a DAX table that compares the values from only one column between the targets ? 

 

 

Thank you .

Hi @Anonymous ,

 

You want to return the line values? for each one? not getting what you mean by


@Anonymous wrote:

..., i want to return all the commun value not for all the columns only but between any column. 

 

 


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



Anonymous
Not applicable

Hi @MFelix 

Thank you so much for your reply ,

I want to highlight the ratio of common values that both targets are sharing in each feature: 

i hope this would be a better example : 

Column1Column2Column3Target
ABC0
DBE0
ASW1
DBW1

 

I want to have this information :

Column1 has 100% common values (There is only A and D in target 0 and target 1)

Column2 has 50%common values( only 1 value exists in target 1 and target0 which is B)

Column3 has 0% common values

...

PS: getting the ratio  or the number of common rows is the same expected output.

 

So what you think ? Could we create a dax table for specefic column each time ? or we can query to resolve this ? 

 

Thank you.

 

Hi @Anonymous ,

 

Sorry for the delay had some work issues, again for this you need to have a different approach because you want to count the values but based also on the unique values on each of the targets.

 

Do you want to return on the table the ratio value then? is that it?

 

 


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



Anonymous
Not applicable

Hi @MFelix ,

Yes Felix that what i want to return. 

Thank you very much.

MFelix
Super User
Super User

Hi @Anonymous ,

 

Sorry for the question but why do you refer that you don't want any measures? Just to understand the requirement and get a more specific answer.

 

Based on the values you present and without using any measures maybe you can do it using a new table using power query or a DAX calculated table. Only using visualizations is difficult because you have more than one requirement you want the duplicates but that are on both of the target if they are duplicates in a single target you won't get it.

 

For this you need to manipulate your data being trough measures or trought a new table otherwise just a plain comparision is difficult.


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



Anonymous
Not applicable

Hi @MFelix ,

 

Thank you for your replay, yes i don't want to use any measures, but I can use  power query since i will be able to regenerate them each time i have a new input data. 

Can you help me create this query ? or DAX calculated table ?

 

Thank you very much

Hi @Anonymous ,

 

Thinking outside the box you can do the following:

 

  • Reference the original table for a new query
  • Unpivot columns all columns except the Target
  • Group row by the Value then chan ge the syntax to the following code:
= Table.Group(#"Unpivoted Columns", {"Value", "Attribute"}, {{"Count", each  Table.RowCount(List.Distinct(_[Target])) , type number}})

Trick is the part of the List.Distinct

  • Filter all rows different from 1
  • Remove the count column
  • Select the attribute column
  • Pivot based on the values
  • Add a custom step refering to the source step
  • Add a custom colum with "DELETE" (or some other text)
  • Filter the new column to have no values (just ot keep the headers of the table
  • Append the previous step with the Pivoted step

Full code below and in attach PBIX.

 

let
    Source = Table,
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(Source, {"Target"}, "Attribute", "Value"),
    #"Grouped Rows" = Table.Group(#"Unpivoted Columns", {"Value", "Attribute"}, {{"Count", each  Table.RowCount(List.Distinct(_[Target])) , type number}}),
    #"Filtered Rows" = Table.SelectRows(#"Grouped Rows", each ([Count] <> 1)),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Count"}),
    #"Pivoted Column" = Table.Pivot(#"Removed Columns", List.Distinct(#"Removed Columns"[Attribute]), "Attribute", "Value", List.Max),
    Custom1 = Source,
    #"Added Custom" = Table.AddColumn(Custom1, "Custom", each "DELETE"),
    #"Filtered Rows1" = Table.SelectRows(#"Added Custom", each ([Custom] <> "DELETE")),
    #"Removed Columns1" = Table.RemoveColumns(#"Filtered Rows1",{"Custom"}),
    #"Appended Query" = Table.Combine({#"Removed Columns1", #"Pivoted Column"})
in
    #"Appended Query"
  •  

Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.