Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
I have a query that pivots the data and then adds some subtotal columns by Summing the values in a few columns.
i.e.
Job Number, cost type A, cost type B,, cost type C, cost type 1, cost type 2, sum A&B&C, sum 1&2, sum A&B&C&1&2
However, when one of the cost types is then missing from the pivot, as they dont have any costs that month, the Sum then errors. Does anyone have a solution that will allow me to use Wildcards for columns names in a forumla, make the columns included in a formula optional, or add a column if it is not already there?
Currently the only solution i can think of is to append the query to a blank query that has all the columns names in it already....
Solved! Go to Solution.
Hi @ZLJ,
You can refer to below formula to check exist columns and add the column not exist:
let Source = Excel.Workbook(File.Contents("C:\Users\xxxxx\Desktop\poivt table with miss type.xlsx"), null, true), Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data], #"Promoted Headers" = Table.PromoteHeaders(Sheet1_Sheet), #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"JobName", type text}, {"Type", type text}, {"Value", Int64.Type}}), #"Pivoted Column" = Table.Pivot(#"Changed Type", List.Distinct(#"Changed Type"[Type]), "Type", "Value", List.Sum), #"Check Exist" = let #"Check Column" = if not Table.HasColumns(#"Pivoted Column","Type A") then Table.AddColumn(#"Pivoted Column", "Type A",each null) else #"Pivoted Column", #"Check Column1" = if not Table.HasColumns(#"Check Column","Type B") then Table.AddColumn(#"Check Column", "Type B",each null) else #"Check Column", #"Check Column2" = if not Table.HasColumns(#"Check Column1","Type C") then Table.AddColumn(#"Check Column1", "Type C",each null) else #"Check Column1", #"Check Column3" = if not Table.HasColumns(#"Check Column2","Type 1") then Table.AddColumn(#"Check Column2", "Type 1",each null) else #"Check Column2", #"Check Column4" = if not Table.HasColumns(#"Check Column3","Type 2") then Table.AddColumn(#"Check Column3", "Type 2",each null) else #"Check Column3" in #"Check Column4" in #"Check Exist"
base table:
After pivot operate:
After "check exist" function:
formula:
#"Check Exist" =
let
#"Check Column" = if not Table.HasColumns(#"Pivoted Column","Type A") then Table.AddColumn(#"Pivoted Column", "Type A",each null) else #"Pivoted Column",
#"Check Column1" = if not Table.HasColumns(#"Check Column","Type B") then Table.AddColumn(#"Check Column", "Type B",each null) else #"Check Column",
#"Check Column2" = if not Table.HasColumns(#"Check Column1","Type C") then Table.AddColumn(#"Check Column1", "Type C",each null) else #"Check Column1",
#"Check Column3" = if not Table.HasColumns(#"Check Column2","Type 1") then Table.AddColumn(#"Check Column2", "Type 1",each null) else #"Check Column2",
#"Check Column4" = if not Table.HasColumns(#"Check Column3","Type 2") then Table.AddColumn(#"Check Column3", "Type 2",each null) else #"Check Column3"
in
#"Check Column4"
Regards,
Xiaoxin Sheng
Hi @ZLJ,
You can refer to below formula to check exist columns and add the column not exist:
let Source = Excel.Workbook(File.Contents("C:\Users\xxxxx\Desktop\poivt table with miss type.xlsx"), null, true), Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data], #"Promoted Headers" = Table.PromoteHeaders(Sheet1_Sheet), #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"JobName", type text}, {"Type", type text}, {"Value", Int64.Type}}), #"Pivoted Column" = Table.Pivot(#"Changed Type", List.Distinct(#"Changed Type"[Type]), "Type", "Value", List.Sum), #"Check Exist" = let #"Check Column" = if not Table.HasColumns(#"Pivoted Column","Type A") then Table.AddColumn(#"Pivoted Column", "Type A",each null) else #"Pivoted Column", #"Check Column1" = if not Table.HasColumns(#"Check Column","Type B") then Table.AddColumn(#"Check Column", "Type B",each null) else #"Check Column", #"Check Column2" = if not Table.HasColumns(#"Check Column1","Type C") then Table.AddColumn(#"Check Column1", "Type C",each null) else #"Check Column1", #"Check Column3" = if not Table.HasColumns(#"Check Column2","Type 1") then Table.AddColumn(#"Check Column2", "Type 1",each null) else #"Check Column2", #"Check Column4" = if not Table.HasColumns(#"Check Column3","Type 2") then Table.AddColumn(#"Check Column3", "Type 2",each null) else #"Check Column3" in #"Check Column4" in #"Check Exist"
base table:
After pivot operate:
After "check exist" function:
formula:
#"Check Exist" =
let
#"Check Column" = if not Table.HasColumns(#"Pivoted Column","Type A") then Table.AddColumn(#"Pivoted Column", "Type A",each null) else #"Pivoted Column",
#"Check Column1" = if not Table.HasColumns(#"Check Column","Type B") then Table.AddColumn(#"Check Column", "Type B",each null) else #"Check Column",
#"Check Column2" = if not Table.HasColumns(#"Check Column1","Type C") then Table.AddColumn(#"Check Column1", "Type C",each null) else #"Check Column1",
#"Check Column3" = if not Table.HasColumns(#"Check Column2","Type 1") then Table.AddColumn(#"Check Column2", "Type 1",each null) else #"Check Column2",
#"Check Column4" = if not Table.HasColumns(#"Check Column3","Type 2") then Table.AddColumn(#"Check Column3", "Type 2",each null) else #"Check Column3"
in
#"Check Column4"
Regards,
Xiaoxin Sheng
Hi Xiaoxin Sheng
I have a very similar issue to this, and I have tried plugging your solution into my report and adjusting the column headers accordingly... but I get a Token Comma Expected error which I can't seem to resolve...
The summary behind my report is that it is a tracker for order items being imported and each item has 1 of 5 statuses...as follows...
1: Awaiting Receipt at Origin
2: On Hand at Origin
3: In Transit to UK
4: Arrived in UK, Awaiting Clearance
5: Delivered to UK DC
As the order items move through the shipping process they move between these statuses in order.
This is not a continual report and is only to track items for 6 months of the year and as such, not all the 5 statuses will be used at a given time. hence the need for this...
Below is my code, with your code plugged in after the #"Pivoted Column" stage and before i reorder the columns etc.
let
Source = #"Article Quantity Lookup",
#"Reordered Columns" = Table.ReorderColumns(Source,{"Item No", "Item Desc", "Transit Status Overview", "PO Warehouse", "Item Qty Shipped", "ETA", "ETA +10 days", "INDC Date", "Shipment Delivery Complete"}),
#"Removed Columns" = Table.RemoveColumns(#"Reordered Columns",{"PO Warehouse"}),
#"Reordered Columns1" = Table.ReorderColumns(#"Removed Columns",{"Item No", "Transit Status Overview", "Item Desc", "Item Qty Shipped", "ETA", "ETA +10 days", "INDC Date", "Shipment Delivery Complete"}),
#"Removed Columns1" = Table.RemoveColumns(#"Reordered Columns1",{"ETA", "ETA +10 days", "INDC Date", "Shipment Delivery Complete"}),
#"Pivoted Column" = Table.Pivot(#"Removed Columns1", List.Distinct(#"Removed Columns1"[#"Transit Status Overview"]), "Transit Status Overview", "Item Qty Shipped", List.Sum),
#"Check Exist" =
let
#"Check Column" = if not Table.HasColumns(#"Pivoted Column","1: Awaiting Receipt at Origin”) then Table.AddColumn(#"Pivoted Column", "1: Awaiting Receipt at Origin",each null) else #"Pivoted Column",
#"Check Column1" = if not Table.HasColumns(#"Check Column","2: On Hand at Origin") then Table.AddColumn(#"Check Column", "2: On Hand at Origin",each null) else #"Check Column",
#"Check Column2" = if not Table.HasColumns(#"Check Column1","3: In Transit to UK") then Table.AddColumn(#"Check Column1", "3: In Transit to UK",each null) else #"Check Column1",
#"Check Column3" = if not Table.HasColumns(#"Check Column2","4: Arrived in UK, Awaiting Clearance") then Table.AddColumn(#"Check Column2", "4: Arrived in UK, Awaiting Clearance",each null) else #"Check Column2",
#"Check Column4" = if not Table.HasColumns(#"Check Column3","5: Delivered to UK DC") then Table.AddColumn(#"Check Column3", "5: Delivered to UK DC",each null) else #"Check Column3"
in
#"Check Column4"
in
#"Check Exist"
#"Reordered Columns2" = Table.ReorderColumns(#"Pivoted Column",{"Item No", "Item Desc", "2: On Hand at Origin", "3: In Transit to UK", "4: Arrived in UK, Awaiting Clearance", "5: Delivered to UK DC"}),
#"Inserted Sum" = Table.AddColumn(#"Reordered Columns2", "Addition", each List.Sum({[#"2: On Hand at Origin"], [#"3: In Transit to UK"], [#"4: Arrived in UK, Awaiting Clearance"], [#"5: Delivered to UK DC"]})),
#"Renamed Columns" = Table.RenameColumns(#"Inserted Sum",{{"Addition", "Total Quantity"}}),
#"Merged Queries" = Table.NestedJoin(#"Renamed Columns", {"Item No"}, #"Article Level Earliest Events", {"Item No"}, "Article Level Earliest Events", JoinKind.LeftOuter),
#"Expanded Article Level Earliest Events" = Table.ExpandTableColumn(#"Merged Queries", "Article Level Earliest Events", {"Earliest ETA", "Earliest ETA +10 days", "Earliest INDC", "Earliest Delivery"}, {"Earliest ETA", "Earliest ETA +10 days", "Earliest INDC", "Earliest Delivery"}),
#"Added Conditional Column" = Table.AddColumn(#"Expanded Article Level Earliest Events", "Available in DC?", each if [Earliest Delivery] = null then "No" else "Yes"),
#"Sorted Rows" = Table.Sort(#"Added Conditional Column",{{"Item No", Order.Ascending}})
in
#"Sorted Rows"
I hope you can help with this as it is frustrating me.
Many thanks in advance
Anthony
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
92 | |
87 | |
84 | |
65 | |
49 |
User | Count |
---|---|
140 | |
114 | |
110 | |
59 | |
59 |