Get the common values of all columns

[Anonymous]

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 A	Column B	Column C	Column D	Column E	Target
50	60	40	30	22	0
60	33	20	30	22	0
50	33	100	33	22	1
22	445	333	333	00	1
600	222	20	333	00	1

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

Column A	Column B	Column C	Column D	Column E	Target
50	33	-	-	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.

[Anonymous]

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 : 

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.

[MFelix]

Hi @Anonymous,

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

[Anonymous]

Hi all, 

Does anyone have an idea ? 

Thank you.

[Anonymous]

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 .

[MFelix]

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. 

[Anonymous]

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 : 

Column1	Column2	Column3	Target
A	B	C	0
D	B	E	0
A	S	W	1
D	B	W	1

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.

[MFelix]

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?

[Anonymous]

Hi @MFelix ,

Yes Felix that what i want to return. 

Thank you very much.

[MFelix]

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.

[Anonymous]

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

[MFelix]

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"

•