How to refresh Dashboard with a revised data source that has deleted columns?

[Annette]

HI Team,

I am using Power BI Desktop and have created a dashboard report. I have just changed my data source by deleting unused columns and also adding one new column. I have deleted these same columns in my Power BI Query to match the excel file data source. Howevr, I have been receiving an error message when I try to refresh my dashboard report and the data does not refresh successfully. 

Please help advise how this error can be corrected so that my data can be refreshed successfully?

Thank you!

Error message:  The Column 'Column33 of the table wasn't found.

Deleted Columns: 
Transaction Type
Eclipse ID
Eclipse MCC
DEC Country
Shipments Net LC
Total Sell Thru ASP USD
Total Sell Thru PRP USD
Deal Indicator (By MCC)

Added Column: Upfront Deal MCC 1

Advanced Editor content:

let

Source = Excel.Workbook(Web.Contents("filename.xlsx"), null, true),
#"Data_Sheet" = Source{[Item="Data",Kind="Sheet"]}[Data],
#"Changed Type" = Table.TransformColumnTypes(#"Data_Sheet",{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}, {"Column5", type text}, {"Column6", type text}, {"Column7", type text}, {"Column8", type text}, {"Column9", type text}, {"Column10", type text}, {"Column11", type text}, {"Column12", type text}, {"Column13", type text}, {"Column14", type text}, {"Column15", type text}, {"Column16", type text}, {"Column17", type text}, {"Column18", type text}, {"Column19", type text}, {"Column20", type text}, {"Column21", type text}, {"Column22", type text}, {"Column23", type any}, {"Column24", type any}, {"Column25", type any}, {"Column26", type any}, {"Column27", type any}, {"Column28", type any}, {"Column29", type any}, {"Column30", type any}, {"Column31", type text}, {"Column32", type text}, {"Column33", type text}, {"Column34", type text}, {"Column35", type text}, {"Column36", type text}, {"Column37", type text}, {"Column38", type text}, {"Column39", type number}}),
#"Promoted Headers" = Table.PromoteHeaders(#"Changed Type", [PromoteAllScalars=true]),
#"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"Fiscal Year", Int64.Type}, {"Fiscal Quarter", type text}, {"Fiscal Week (In Year)", type text}, {"Seller Sub Region3", type text}, {"Seller Country", type text}, {"Buyer Sub Region3", type text}, {"Buyer Country", type text}, {"Transaction Type", type text}, {"Seller Siebel ID", type text}, {"Seller Siebel English Name", type text}, {"Buyer Siebel ID", type text}, {"Buyer Siebel English Name", type text}, {"Current Product Group", type text}, {"Current PL", type text}, {"Product Number", type text}, {"Product Name", type text}, {"Eclipse ID", Int64.Type}, {"Eclipse MCC", type text}, {"Deal Indicator Group", type text}, {"DEC Name", type text}, {"DEC Country", type text}, {"Segment Group", type text}, {"Shipments Net USD", Int64.Type}, {"Shipments Net LC", Int64.Type}, {"Shipments Total Quantity", Int64.Type}, {"Total Sell Thru ASP USD", type number}, {"Total Sell Thru Total Quantity", Int64.Type}, {"Total Sell Thru PRP USD", type number}, {"Total Sell Thru Deal @Net USD", type number}, {"TOTAL Sell Thru PAY@NET", type number}, {"Seller Sub Region", type text}, {"Buyer Sub Region", type text}, {"Current Aruba Membership", type text}, {"Current Aruba Distributor", type text}, {"Product Group L1", type text}, {"Product Category", type text}, {"Deal Indicator #(lf)(By MCC)", type text}, {"D2D", type text}, {"1.02", type number}}),
#"Removed Columns" = Table.RemoveColumns(#"Changed Type1",{"Transaction Type", "Eclipse ID"}),
#"Reordered Columns" = Table.ReorderColumns(#"Removed Columns",{"Fiscal Year", "Fiscal Quarter", "Fiscal Week (In Year)", "Seller Sub Region3", "Seller Country", "Buyer Sub Region3", "Buyer Country", "Seller Siebel ID", "Seller Siebel English Name", "Buyer Siebel ID", "Buyer Siebel English Name", "Current Product Group", "Current PL", "Product Number", "Product Name", "Deal Indicator Group", "Eclipse MCC", "DEC Name", "DEC Country", "Segment Group", "Shipments Net USD", "Shipments Net LC", "Shipments Total Quantity", "Total Sell Thru ASP USD", "Total Sell Thru Total Quantity", "Total Sell Thru PRP USD", "Total Sell Thru Deal @Net USD", "TOTAL Sell Thru PAY@NET", "Seller Sub Region", "Buyer Sub Region", "Current Aruba Membership", "Current Aruba Distributor", "Product Group L1", "Product Category", "Deal Indicator #(lf)(By MCC)", "D2D", "1.02"}),
#"Renamed Columns" = Table.RenameColumns(#"Reordered Columns",{{"Eclipse MCC", "Upfront Deal MCC 1"}})

in
#"Renamed Columns"

[Jimmy801 · Accepted solution]

Hello

as you reading from a sheet from Excel-file, that is a unstructured data source, Power BI loads the usedrange of the sheet and the wizard as a standard tries to identify the column type. In this step you have your whole usedrange hardcoded in column names. So, whenever you reducing the columns in the usedrange you will get an error. Therefore to solve this issue, as first step delete the step #"Changed Type". The second thing to do is to delete the hard-coded columns in this step #"Changed Type1" that you did delete like Transaction Type and add your new column.

If you still have problems, don't hesitate to contact me.

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy

[Annette · Accepted solution]

Hi Jimmy,

Many thanks for helping us with this suggested solution. I still need some help.

After I changed the Advanced Query based on your instructions, I got this error message. Did I miss anything in this and how do I corrct this please? Thanks so much!

Error message:

The import Changed Type matches no exports. Did you miss a module reference?

Advance Editor - Revised:

let

Source = Excel.Workbook(Web.Contents("https://hpe.sharepoint.com/teams/APJ%20HPE%20Aruba%20Channels/Shared%20Documents/Distribution/APJ%20..."), null, true),
#"Data_Sheet" = Source{[Item="Data",Kind="Sheet"]}[Data],
#"Promoted Headers" = Table.PromoteHeaders(#"Changed Type", [PromoteAllScalars=true]),
#"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"Fiscal Year", Int64.Type}, {"Fiscal Quarter", type text}, {"Fiscal Week (In Year)", type text}, {"Seller Sub Region3", type text}, {"Seller Country", type text}, {"Buyer Sub Region3", type text}, {"Buyer Country", type text}, {"Seller Siebel ID", type text}, {"Seller Siebel English Name", type text}, {"Buyer Siebel ID", type text}, {"Buyer Siebel English Name", type text}, {"Current Product Group", type text}, {"Current PL", type text}, {"Product Number", type text}, {"Product Name", type text}, {"Deal Indicator Group", type text}, {"Upfront Deal MCC 1", type text}, {"DEC Name", type text}, {"Segment Group", type text}, {"Shipments Net USD", Int64.Type}, {"Shipments Total Quantity", Int64.Type}, {"Total Sell Thru Total Quantity", type number}, {"Total Sell Thru Deal @Net USD", type number}, {"TOTAL Sell Thru PAY@NET", type number}, {"Seller Sub Region", type text}, {"Buyer Sub Region", type text}, {"Current Aruba Membership", type text}, {"Current Aruba Distributor", type text}, {"Product Group L1", type text}, {"Product Category", type text}, {"D2D", type text}, {"1.02", type number}})

in
#"Changed Type1"

[Jimmy801 · Accepted solution]

Hello

as you reading from a sheet from Excel-file, that is a unstructured data source, Power BI loads the usedrange of the sheet and the wizard as a standard tries to identify the column type. In this step you have your whole usedrange hardcoded in column names. So, whenever you reducing the columns in the usedrange you will get an error. Therefore to solve this issue, as first step delete the step #"Changed Type". The second thing to do is to delete the hard-coded columns in this step #"Changed Type1" that you did delete like Transaction Type and add your new column.

If you still have problems, don't hesitate to contact me.

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy

[Annette · Accepted solution]

Hi Jimmy,

Many thanks for helping us with this suggested solution. I still need some help.

After I changed the Advanced Query based on your instructions, I got this error message. Did I miss anything in this and how do I corrct this please? Thanks so much!

Error message:

The import Changed Type matches no exports. Did you miss a module reference?

Advance Editor - Revised:

let

Source = Excel.Workbook(Web.Contents("https://hpe.sharepoint.com/teams/APJ%20HPE%20Aruba%20Channels/Shared%20Documents/Distribution/APJ%20..."), null, true),
#"Data_Sheet" = Source{[Item="Data",Kind="Sheet"]}[Data],
#"Promoted Headers" = Table.PromoteHeaders(#"Changed Type", [PromoteAllScalars=true]),
#"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"Fiscal Year", Int64.Type}, {"Fiscal Quarter", type text}, {"Fiscal Week (In Year)", type text}, {"Seller Sub Region3", type text}, {"Seller Country", type text}, {"Buyer Sub Region3", type text}, {"Buyer Country", type text}, {"Seller Siebel ID", type text}, {"Seller Siebel English Name", type text}, {"Buyer Siebel ID", type text}, {"Buyer Siebel English Name", type text}, {"Current Product Group", type text}, {"Current PL", type text}, {"Product Number", type text}, {"Product Name", type text}, {"Deal Indicator Group", type text}, {"Upfront Deal MCC 1", type text}, {"DEC Name", type text}, {"Segment Group", type text}, {"Shipments Net USD", Int64.Type}, {"Shipments Total Quantity", Int64.Type}, {"Total Sell Thru Total Quantity", type number}, {"Total Sell Thru Deal @Net USD", type number}, {"TOTAL Sell Thru PAY@NET", type number}, {"Seller Sub Region", type text}, {"Buyer Sub Region", type text}, {"Current Aruba Membership", type text}, {"Current Aruba Distributor", type text}, {"Product Group L1", type text}, {"Product Category", type text}, {"D2D", type text}, {"1.02", type number}})

in
#"Changed Type1"

[Jimmy801 · Accepted solution]

Hello @Annette 

change this line

#"Promoted Headers" = Table.PromoteHeaders(#"Changed Type", [PromoteAllScalars=true]),

into

#"Promoted Headers" = Table.PromoteHeaders(#"Data_Sheet", [PromoteAllScalars=true]),

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy

[Annette]

Hi Jimmy,

Thank you so much for the help in providing this solution ! It finally works! 

You have saved me so mmuch time researching on how to resolve it.

You have been so helpful and we need more advisors like you! 

Appreciate it!

Warm Regards, Annette Chua.

[Jimmy801]

Hello @Annette 

your feedback is very very appreciated

All the best

Jimmy