Pivot column total failing if any of the pivot column missing

[Anonymous]

Hello ,

Please advice on the below issue.

Before pivot source table

File #	Name	Earnings code	Earnings Amount
100007	null	CMM COMMISSION	100
100007	Null	Commision 2	1500
100040	Null	CMM COMMISSION	2614
100040	Null	Commision 2	0

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

File #	Name	Reg. Hours	Reg. Earnings	CMM COMMISSION	Commision 2	Total(Cmmcomm +comm2)
100007	null	40	1000	100	1500	1600
100040	null	40	20550	2614	0	2614.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.

[Jakinta · Accepted solution]

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

[Anonymous · Accepted solution]

Hello Jakinta,

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

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

[Jakinta · Accepted solution]

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