Join tables to create matrix like view od data map

[Khostri]

Hi all,

i am pretty new to Power Query. I just need to simulate similar result as with Excel (but Excel solution is really slow).

I have one big table, already cleaned, polished in Power Query, looking like that (with other columns):

 SourceList

name	product	Other columns with data
pc1	SQL 2014	data
PC1	Office 2013	data
PC1	.NET	data
PC1	ESET	data
PC1	Exchange 2013	data
PC1	Acrobat	data
pc2	SQL 2016	data
PC2	.NET	data
PC2	ESET	data
PC2	Office 2016	data
PC2	Acrobat	data
PC3	Office 2010	data

Next I have table with list of programs to monitor: (sorted by group descending and secondly name descending)

 Monitored

name	group
SQL 2016	200
SQL 2014	200
SQL 2012	200
Office 2016	180
Office 2013	180
Office 2010	180
Exchange 2016	160
Exchange 2013	160
Exchange 2010	160

This table doesn't have fixed number of rows, it can dynamically change.

I need to combine two tables to looks like that:

Result

name	Other Columns with Data	SQL 2016	SQL 2014	SQL 2012	Office 2016	Office 2013	Office 2010	Exchange 2016	Exchange 2013	Exchange 2010
pc1	data	0	1	0	0	1	0	0	1	0
PC2	data	1	0	0	1	0	0	0	0	0
PC3	data	0	0	0	0	0	1	0	0	0

So name is deduplicated, rows from Monitored are transposed to columns in Result (number of transposed columns is dynamic, for example from 5 to 300 columns based on Monitored) and in each column there is a value "1" if Product in current column exist on curent PC name (and if not, then 0 or null)

I cannot figure how to do it. I am able to do it in Excel by pure formulas (without VBA), but not sure in Power Query. I have tried to check values against columns names, but don't know how to handle dynamic number of columns.

In Excel in looks like that (Result table formula in each cell):

=ABS(COUNTIFS(Installation!$F:$F;map!$A5;Installation!$I:$I;map!M$1)>0)

Count number of values where Name from current row is found in table Source list and value in Product column equals current Column header.

For easier understanding there I try to change formula to presented tables in this post:

So for column SQL 2016 it would looks like:

=ABS(COUNTIFS(SourceList[[#Data];[Name]];Result[@[Name]];SourceList[[#Data];[Product];Result[[#Headers];[SQL 2016]])>0)

But the header name "SQL 2016"  in Excel change for each additional column (its filled in Sheet so formula just takes first row value for comparsion and it doesnt matter how many columns are there). I am using countifs as source for boolean comparsion and then only true or false is result in Excel converted to numeric

After I will be able to figure first step, I will need the second one:

Create new table based on result, but each value would be calculated diffrently based on group value from table Monitored and Other data.

So :

Adjusted

name	SQL 2016	SQL 2014	SQL 2012	Office 2016	Office 2013	Office 2010	Exchange 2016	Exchange 2013	Exchange 2010
pc1	CalcGroup200	CalcGroup200	CalcGroup200	CalcGroup180	CalcGroup180	CalcGroup180	CalcGroup160	CalcGroup160	CalcGroup160
PC2	CalcGroup200	CalcGroup200	CalcGroup200	CalcGroup180	CalcGroup180	CalcGroup180	CalcGroup160	CalcGroup160	CalcGroup160
PC3	CalcGroup200	CalcGroup200	CalcGroup200	CalcGroup180	CalcGroup180	CalcGroup180	CalcGroup160	CalcGroup160	CalcGroup160

And in each group calculation is from rightmost column of each group to the leftmost group column - so for example SQL:

1. SQL 2012 Calculation is used, based on data from Result (if value in Result is 1, then calculate value Adjusted based on Other data)
2. Then SQL 2014 - if value is 1, then calculation based on Other data and computed result SQL 2016 in Adjusted

Please anyone can help me with this problem or have examples how to do it?

I just started to think for example about using map as bits - so for each value in Monitored add bit value, probably descending (number of rows is max exponent, then each value has number like 1,2,4,8,16,32 but in descending order). Then map can be created by adding joined value to table, sum it, then convert to binary, add leading characters and separate binary to each column. But not sure how to realize it in Power Query. Is it right idea?

Thank you anyone for help

[v-huizhn-msft · Accepted solution]

Hi @Khostri,

I type the two tables in Power BI, and create a relationship between them as follows.



Then I create a measure using the formula.

Measure = COUNTA(T1[product])+0

Create a matrix visual, select the T1[name] as rows, the T2[name] as columns, you will get expected result.



Best Regards,
Angelia