Unpivot Columns without duplicating data in Power Query

[smazo]

Hello dear colleagues:

I'm running into a unique problem with my large payroll dataset that is affecting how other numbers are calculated. 

In short, I have a table that looks like this (this is an example of how my table looks):

When I unpivot the bonus categories in Power Query (so it can show up cleaner in my BI models) it duplicates data that messes up other models by summing up numbers that shouldn't be summed up. This is what the table looks like after unpivoting the bonus columns:

This inturn miscalcuates the hours work and the gross pay in other parts of my model for a single table. What would be a quick/easy/doable way to make the table in Power Query look something like this instead, so that column total for total hours worked and Gross pay don't get affected?

Thank you in advance for any solutions that might help! 

[Manjit_smv]

I am getting the same problem while unpivoting, have you found any solution. Please share 

[Ahmedx]

pls try this

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jdFJDoMwDAXQu2RtVR5wQ5ctvQXK/a9Ru3RwcItiwSdIPOUrrGu52oBHgYJysouRxV6IBO0xebB6bneDHhEGNM8fcyDqiLjZgMe+WEX/SPRQdK3GRGx1yYJxM4sNeOx7nVmfR5V2iaLvpRgFvVbJ1JFd7jbgkX4k458jjqQrRpqJ4A8Um9H0RYRv5qvWHg==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Name = _t, PayDate = _t, #"Gross Pay" = _t, #"Hours Worked" = _t, #"General Bonus" = _t, #"Summer Bonus" = _t, #"Pickup Bonus" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Name", type text}, {"PayDate", type date}, {"Gross Pay", Int64.Type}, {"Hours Worked", Int64.Type}, {"General Bonus", Int64.Type}, {"Summer Bonus", Int64.Type}, {"Pickup Bonus", Int64.Type}}),
    #"Grouped Rows" = 
    Table.Group(#"Changed Type", {"Name", "PayDate"},
                               {{"Count", (x)=> Table.AddColumn(
                                                  Table.AddColumn( 
                                                     Table.AddIndexColumn(
                                                       Table.UnpivotOtherColumns(x, {"Name", "PayDate", "Gross Pay", "Hours Worked"}, "Attribute", "Value")
                                                    ,"Index", 1, 1, Int64.Type),
                                                  "_Gross Pay", each if [Index] > 1 then 0 else [Gross Pay]),
                                                  "_Hours Worked", each if [Index] > 1 then 0 else [Hours Worked])}}),
    #"Expanded Count" = Table.ExpandTableColumn(#"Grouped Rows", "Count", {"Attribute", "Value", "_Gross Pay", "_Hours Worked"}, {"Attribute", "Value", "_Gross Pay", "_Hours Worked"})
in
    #"Expanded Count"

[smazo]

Hi Ahmedx,

I cannot share the data table I am working with, but what I did was example. I am working with something like 43 colmuns, 4 date columns, 6 text columns, 33 number columns (of which 7 need to be unpivoted). Also your code didn't work.

[smazo]

Hi Ahmedx,

Could you please explain the steps you took in the code shown above? I'm having a hard time understanding what you did. I understand that you used Power Query M, but how would that look like in the actual Power Query window on Power BI?

[Ahmedx]

Share sample pbix file to help you.

[Ashish_Mathur]

Hi,

Those measures will have to be edited to account for the transformed dataset.

[smazo]

Hi Ashish,

That's great idea. Could you please give an example of how I would do that? I'm having a hard time visualizing the implications of your solution.

[Ashish_Mathur]

Hi,

Share some data to work with and show the expected result.

[amitchandak]

@smazo , Those all are measures you do need to break them, in case you need slicer, you can consider field parameters or calculations groups

Power BI Field Parameters — A Quick way for Dynamic Visuals: https://amitchandak.medium.com/power-bi-field-parameters-a-quick-way-for-dynamic-visuals-fc4095ae9af...
Power BI Field Parameters- Measure Slicer and Axis/Dimension slicer: https://youtu.be/lqF3Wa1FllE
Switch TOPN with Field Parameters: https://amitchandak.medium.com/switch-topn-with-field-parameters-299a0ae3725f

Calculation Groups- Measure Slicer, Measure Header Grouping, Measure to dimension conversion. Complex Table display : https://youtu.be/qMNv67P8Go0

If you want the split text in the name

Power BI- Text Part slicer to filter/search text - https://youtu.be/MKKWeOqFG4c
https://medium.com/@amitchandak/power-bi-search-text-parts-in-text-ad2f9783ffa2