Re-create excel view in power bi - pivot / unpivot

[brickanalyst]

Hello everyone,

I spent quite time for a tab which I thought it would be easy to get it done. 
I'd like to mention what it's requested and please ask me any questions if you have

suppose we have a tab in pbi and data is connected to excel (live)
I don't have access to the excel file, but I have 2 tables with same structure
coming from sql db and somehow I need to bring the same view as it was before

today's view (excel live)

imagine there are 300 categories, they have unique codes and english meanings
and every column is a new record ( column 1 = project 1, column 2 = project 2 etc)

table1_values is coming from all values from category1 in table1 above (sum=35)
table2_values is coming from the same way (sum=40) Then, basic variance and when you compare these two what's min, max etc.

And I have two pivoted tables that comes from db server below

like i said there are 300 categories, however i will get numeric values to aggregate.

I don't know how I could structure and do calculations on it (average,sum)
but I was trying to do this below:

1) find unique values (for example cat_code = cat1, cat2 etc)
2) do unpivot so cat_code so they all be in under one row

3) and do aggregation on values just like excel ? dynamically as a row ?

I just couldn't get it done. 
Please share any idea, video link etc.

thank you

[Anonymous · Accepted solution]

Hi,@brickanalyst 

Regarding the issue you raised, my solution is as follows:

1.First I have created the following table and the column names and data are the data you have given:

2.Then use the following m language in powerquery to turn the column name into a column of values and remove unnecessary values:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WykksLilPTc3WNTBU0lFKLE5JA2Egs6AoPys1uQQkmJQMJJ2cgISKoYEBkDKyBJOmekamSrE6yIYYAcWTi9NSgVRaeSrInNRyMA+n9lgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Filename = _t, hashcode = _t, #"Column uid" = _t, cat1 = _t, cat2 = _t, cat3 = _t, cat4 = _t, cat5 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Filename", type text}, {"hashcode", type text}, {"Column uid", type text}, {"cat1", type text}, {"cat2", type text}, {"cat3", Currency.Type}, {"cat4", Int64.Type}, {"cat5", type number}}),
    #"name"=Table.ColumnNames(#"Changed Type"),
    #"nametable"=Table.FromList(#"name"
    ,Splitter.SplitByNothing(),{"1"}),
    #"Removed Top Rows" = Table.Skip(nametable,3)
in
    #"Removed Top Rows"

3.Change the values of two tables from rows to columns using transpose in powerquery:

4.Then obtain the values of the other two tables according to your requirements using the following calculation columns:

table1-value = IF('Table1'[1]="cat1",SUM('cat1 table1'[Column1]))

table2-value = IF('Table1'[1]="cat1",SUM('cat1 table2'[Column1]))

variance = 'Table1'[table1-value]-'Table1'[table2-value]

5.Insert a sequence for two tables to generate a new table, table1vstable2, later with a merge query:

6.Use the following calculation columns to achieve the results of the last few columns:

Column = 'table1vstable2'[Column1.1]-'table1vstable2'[Column1]

max = IF('Table1'[1]="cat1",MAX('table1vstable2'[Column]))

min = IF('Table1'[1]="cat1",MIN('table1vstable2'[Column]))

var = IF('Table1'[1]="cat1",VAR.S(table1vstable2[Column]))

7.Here's my final result, which I hope meets your requirements.

Please find the attached pbix relevant to the case.

Best Regards,

Leroy Lu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[brickanalyst]

Hi @Anonymous

Unfortunately it's not working for each category for the table1 records.
yes we have for cat1 values but when I want to go for cat2 I had to create another calculated column?
I need something dynamic so I believe I need a measure so that whole calculatation will be divided into categoreis (dimensions)

could you help me on that?

[Anonymous · Accepted solution]

HI@brickanalyst 
Thank you for your quick reply.

The answer to your question is that most of the calculated columns I gave in the previous post don't need to be re-created, which can be solved with the switch () function, but because your data comes from different tables, the code for calculating columns needs to be modified according to the data, and because of the data structure, The steps for powerquery in the above post cannot be optimized either. Here is my updated solution based on your needs:

1.First I created two more data tables:

2.Then I repeated the third and fifth steps of the previous post:

3.Create calculated column references:

max = SWITCH(TRUE(),
'Table1'[1]="cat1",MAX('table1vstable2'[Column]),
'Table1'[1]="cat2",MAX('table1vstable2 cat2'[Column]),
0)

min = SWITCH(TRUE(),
'Table1'[1]="cat1",MIN('table1vstable2'[Column]),
'Table1'[1]="cat2",MIN('table1vstable2 cat2'[Column]),0
)

table1-value = SWITCH(TRUE(),'Table1'[1]="cat1",SUM('cat1 table1'[Column1]),
'Table1'[1]="cat2",SUM('cat2 table1'[Column1]),0
)

table2-value = SWITCH(TRUE(),'Table1'[1]="cat1",SUM('cat1 table2'[Column1]),
'Table1'[1]="cat2",SUM('cat2 table2'[Column1]),0)

var = SWITCH(TRUE(),'Table1'[1]="cat1",VAR.S(table1vstable2[Column]),
'Table1'[1]="cat2",VAR.S('table1vstable2 cat2'[Column]),0
)

4.Here's my final result, which I hope meets your requirements.

5.Here is the relevant documentation:

SWITCH function (DAX) - DAX | Microsoft Learn

Please find the attached pbix relevant to the case.

Best Regards,

Leroy Lu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[brickanalyst]

I found the solution on my end, thank you for confirming ! 

[brickanalyst]

it looks good. I will try to implement on my task, thank you !

[Anonymous · Accepted solution]

Hi,@brickanalyst 

Regarding the issue you raised, my solution is as follows:

1.First I have created the following table and the column names and data are the data you have given:

2.Then use the following m language in powerquery to turn the column name into a column of values and remove unnecessary values:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WykksLilPTc3WNTBU0lFKLE5JA2Egs6AoPys1uQQkmJQMJJ2cgISKoYEBkDKyBJOmekamSrE6yIYYAcWTi9NSgVRaeSrInNRyMA+n9lgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Filename = _t, hashcode = _t, #"Column uid" = _t, cat1 = _t, cat2 = _t, cat3 = _t, cat4 = _t, cat5 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Filename", type text}, {"hashcode", type text}, {"Column uid", type text}, {"cat1", type text}, {"cat2", type text}, {"cat3", Currency.Type}, {"cat4", Int64.Type}, {"cat5", type number}}),
    #"name"=Table.ColumnNames(#"Changed Type"),
    #"nametable"=Table.FromList(#"name"
    ,Splitter.SplitByNothing(),{"1"}),
    #"Removed Top Rows" = Table.Skip(nametable,3)
in
    #"Removed Top Rows"

3.Change the values of two tables from rows to columns using transpose in powerquery:

4.Then obtain the values of the other two tables according to your requirements using the following calculation columns:

table1-value = IF('Table1'[1]="cat1",SUM('cat1 table1'[Column1]))

table2-value = IF('Table1'[1]="cat1",SUM('cat1 table2'[Column1]))

variance = 'Table1'[table1-value]-'Table1'[table2-value]

5.Insert a sequence for two tables to generate a new table, table1vstable2, later with a merge query:

6.Use the following calculation columns to achieve the results of the last few columns:

Column = 'table1vstable2'[Column1.1]-'table1vstable2'[Column1]

max = IF('Table1'[1]="cat1",MAX('table1vstable2'[Column]))

min = IF('Table1'[1]="cat1",MIN('table1vstable2'[Column]))

var = IF('Table1'[1]="cat1",VAR.S(table1vstable2[Column]))

7.Here's my final result, which I hope meets your requirements.

Please find the attached pbix relevant to the case.

Best Regards,

Leroy Lu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.