Forum Discussion
Unpivot Columns without duplicating data in Power Query
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...
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"