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 Sage
Solution Sage

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 Sage
Solution Sage

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 Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Get Ready for Power BI Dev Camp

Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.

Get Ready for Power BI Dev Camp

Power BI Dev Camp - September 30th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!

PowerPlatform 768x460.png

Microsoft Learn

Check out our new Discover Your Career Path blog post series and get all the details.

Top Kudoed Authors