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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
brickanalyst
Frequent Visitor

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

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)

brickanalyst_0-1718114064451.png

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

brickanalyst_1-1718114241512.png

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

 

 

2 ACCEPTED SOLUTIONS
v-linyulu-msft
Community Support
Community Support

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:

vlinyulumsft_0-1718184591609.png

vlinyulumsft_2-1718184616872.png

vlinyulumsft_4-1718184640806.png

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"

vlinyulumsft_5-1718184675310.png

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

vlinyulumsft_6-1718184689741.png

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:

vlinyulumsft_7-1718184760612.png

vlinyulumsft_8-1718184768135.png

vlinyulumsft_9-1718184780841.png

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.

vlinyulumsft_10-1718184854636.png

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.

 

 

View solution in original post

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:

vlinyulumsft_0-1718520343274.pngvlinyulumsft_1-1718520349883.png

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

vlinyulumsft_2-1718520367478.png

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.

vlinyulumsft_3-1718520450961.png

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.

 

View solution in original post

5 REPLIES 5
brickanalyst
Frequent Visitor

Hi @v-linyulu-msft

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?

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:

vlinyulumsft_0-1718520343274.pngvlinyulumsft_1-1718520349883.png

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

vlinyulumsft_2-1718520367478.png

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.

vlinyulumsft_3-1718520450961.png

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.

 

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

brickanalyst
Frequent Visitor

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

v-linyulu-msft
Community Support
Community Support

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:

vlinyulumsft_0-1718184591609.png

vlinyulumsft_2-1718184616872.png

vlinyulumsft_4-1718184640806.png

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"

vlinyulumsft_5-1718184675310.png

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

vlinyulumsft_6-1718184689741.png

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:

vlinyulumsft_7-1718184760612.png

vlinyulumsft_8-1718184768135.png

vlinyulumsft_9-1718184780841.png

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.

vlinyulumsft_10-1718184854636.png

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.

 

 

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

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

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.