cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
singamsagar
Frequent Visitor

Pivot column total failing if any of the pivot column missing

Hello ,

Please advice on the below issue.

Before pivot source table

File #NameEarnings codeEarnings Amount
100007nullCMM COMMISSION100
100007NullCommision 21500
100040NullCMM COMMISSION2614
100040NullCommision 20

 

After merging(left outer join)  with above table and pivoting.

File #NameReg. HoursReg. EarningsCMM COMMISSIONCommision 2Total(Cmmcomm +comm2)
100007null40100010015001600
100040null4020550261402614.69

 

New column is Total , my problem is there is a scenarion some pivot columns may not exist in future that time this total will be failed due to non availabilty of missing column. But this shouldn't be  the total is combination of multiple columns.

2 ACCEPTED SOLUTIONS
Jakinta
Solution Specialist
Solution Specialist

Hi,

 

2nd source table is missing, but anyway maybe this can help in transforming your 1st table.

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQAAnMlHaW80pwcIOXs66vg7O/r6xkc7OnvBxQAKlCK1UFS6AdVmJ+bm1mcmZ+nYARSZUpQmSmQZwxXY2KApAbdTiMzQxPsKlEsNSCgxhzsfD1zc6XYWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"File #" = _t, Name = _t, #"Earnings code" = _t, #"Earnings Amount" = _t]),
    ChangedType = Table.TransformColumnTypes(Source,{{"Earnings Amount", type number}}),
    Pivoted = Table.Pivot(ChangedType, List.Distinct(ChangedType[#"Earnings code"]), "Earnings code", "Earnings Amount"),
    Cols = Table.ColumnNames(Pivoted),
    Grouped = Table.Group(Pivoted, {"File #"}, {{ "GR", each Table.FirstN( Table.FillUp(_, Cols ),1) }}),
    Removed = Table.RemoveColumns(Grouped,{"File #"}),
    Expanded = Table.ExpandTableColumn(Removed, "GR", Cols),
    Total = Table.AddColumn(Expanded, "Total", each List.Sum(List.Skip(Record.ToList(_),2)) )
in
    Total

 

Jakinta_1-1623581466919.png

View solution in original post

singamsagar
Frequent Visitor

Hello Jakinta,

Appreciated your reply. Thankyou . Below is the second table. I will use your code and work.

singamsagar_0-1623688364700.png

 

My issue - After pivoting with left outer join with File# , in futute commisions or any other columns(after piviot) may be there or may not in that case my total will get fail due to missing columns. For this i found one more solution creating dynamic columns( creating all the required columns with out data and append this dynamic columns. when ever any missing column this will help to overwrite and avoid getting fail while doing addition

 

View solution in original post

2 REPLIES 2
singamsagar
Frequent Visitor

Hello Jakinta,

Appreciated your reply. Thankyou . Below is the second table. I will use your code and work.

singamsagar_0-1623688364700.png

 

My issue - After pivoting with left outer join with File# , in futute commisions or any other columns(after piviot) may be there or may not in that case my total will get fail due to missing columns. For this i found one more solution creating dynamic columns( creating all the required columns with out data and append this dynamic columns. when ever any missing column this will help to overwrite and avoid getting fail while doing addition

 

View solution in original post

Jakinta
Solution Specialist
Solution Specialist

Hi,

 

2nd source table is missing, but anyway maybe this can help in transforming your 1st table.

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQAAnMlHaW80pwcIOXs66vg7O/r6xkc7OnvBxQAKlCK1UFS6AdVmJ+bm1mcmZ+nYARSZUpQmSmQZwxXY2KApAbdTiMzQxPsKlEsNSCgxhzsfD1zc6XYWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"File #" = _t, Name = _t, #"Earnings code" = _t, #"Earnings Amount" = _t]),
    ChangedType = Table.TransformColumnTypes(Source,{{"Earnings Amount", type number}}),
    Pivoted = Table.Pivot(ChangedType, List.Distinct(ChangedType[#"Earnings code"]), "Earnings code", "Earnings Amount"),
    Cols = Table.ColumnNames(Pivoted),
    Grouped = Table.Group(Pivoted, {"File #"}, {{ "GR", each Table.FirstN( Table.FillUp(_, Cols ),1) }}),
    Removed = Table.RemoveColumns(Grouped,{"File #"}),
    Expanded = Table.ExpandTableColumn(Removed, "GR", Cols),
    Total = Table.AddColumn(Expanded, "Total", each List.Sum(List.Skip(Record.ToList(_),2)) )
in
    Total

 

Jakinta_1-1623581466919.png

View solution in original post

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

July 2021 Update 768x460.png

Check it out!

Click here to read more about the July 2021 Updates

Top Kudoed Authors