Changing Column Names

[darylmitchell]

I have a weekly excel file of Monthly finance data.
The issue I have is the columns change.

As we go through the year the "E" after the month will turn to "A"  e.g. Last Month the 10th column would have been "Oct E (Sav)"

Jan A (Sav)

Feb A (Sav)

Mar A (Sav)

Apr A (Sav)

May A (Sav)

Jun A (Sav)

Jul A (Sav)

Aug A (Sav)

Sep A (Sav)

Oct A (Sav)

Nov E (Sav)

Dec E (Sav)

I have already handled this by unpivoting these columns etc and also using Mquery functions like 
= Table.TransformColumnTypes(#"Removed Top Rows",List.Transform(Table.ColumnNames(#"Removed Top Rows"), each {_ , type text}))
To get the column types changed, referncing the name dynamically without had coding any names.

This all works fine in the desktop version of PowerBI.
But when I pass this to Enterprise to run automatically it fails as there seems to be some residual caching of column names.
Anyone know anything about this or had the same issue?

[BA_Pete]

Hi @darylmitchell ,

Are you able to share your full M query please?

Make sure to anonymise any sensitive information such as server names/file paths etc.

Power Query won't be caching any column names, so my guess is that you've got a rogue reference somewhere that's invoking them - it may be causing an in-cell error in Desktop that's not easily noticeable, but failing the refresh in the Service.

Pete

[darylmitchell]

Hi Pete.
Here are the column formats from different days.  The content isn't the issue only the column headers.
In PBI desktop running on my PC it seems to handle it, on enterprise it doesn't.

I know all possibilities there can be but have no control over the formats that are actually sent or the number of columns in the sheet.

11/10/2023	16-Oct	30-Oct	02/11/2023
Action #	Action #	Action #	Action #
Fiscal Year	Fiscal Year	Fiscal Year	Fiscal Year
Status/Risk	Status/Risk	Status/Risk	Status/Risk
RBU	RBU	RBU	RBU
Country Cluster	Country Cluster	Country Cluster	Country Cluster
Country	Country	Country	Country
Strategic Unit	Strategic Unit	Strategic Unit	Strategic Unit
Business Line	Business Line	Business Line	Business Line
Service Type	Service Type	Service Type	Service Type
Service Unit	Service Unit	Service Unit	Service Unit
Unit	Unit	Unit	Unit
Delivery Owner	Delivery Owner	Delivery Owner	Delivery Owner
Designee	Designee	Designee	Designee
Finance Owner	Finance Owner	Finance Owner	Finance Owner
Action Title	Action Title	Action Title	Action Title
Type	Type	Type	Type
Impact Type	Impact Type	Impact Type	Impact Type
P&L Benefit	P&L Benefit	P&L Benefit	P&L Benefit
P&L Beneficiary	P&L Beneficiary	P&L Beneficiary	P&L Beneficiary
Global Initiative	Global Initiative	Global Initiative	Global Initiative
Is Lean Supported	Is Lean Supported	Is Lean Supported	Is Lean Supported
Reporting Category	Reporting Category	Reporting Category	Reporting Category
Global Program	Global Program	Global Program	Global Program
Finance Approval	Finance Approval	Finance Approval	Finance Approval
Redeployment Approval	Redeployment Approval	Redeployment Approval	Redeployment Approval
Category	Category	Category	Category
Sub Category	Comments	Sub Category	Sub Category
Employment Status	Sub Category	Employment Status	Employment Status
Free Up Type	Employment Status	Free Up Type	Free Up Type
Transfer Location	Free Up Type	Transfer Location	Transfer Location
Offshore Site	Transfer Location	Offshore Site	Offshore Site
Offshore Qty	Offshore Site	Offshore Qty	Offshore Qty
Offshore Monthly Cost In	Offshore Qty	Offshore Monthly Cost In	Offshore Monthly Cost In
Offshore Monthly Cost Out	Offshore Monthly Cost In	Offshore Monthly Cost Out	Offshore Monthly Cost Out
Rev Monthly	Offshore Monthly Cost Out	Rev Monthly	Rev Monthly
PM Monthly	Rev Monthly	PM Monthly	PM Monthly
GM Monthly	PM Monthly	GM Monthly	GM Monthly
Contract Id	GM Monthly	Contract Id	Contract Id
Rev/Cost P&L Cat	Contract Id	Rev/Cost P&L Cat	Rev/Cost P&L Cat
Customer/Account	Rev/Cost P&L Cat	Customer/Account	Customer/Account
Customer Code	Customer/Account	Customer Code	Customer Code
RED Account	Customer Code	RED Account	RED Account
Customer  Assigment [%]	RED Account	Customer  Assigment [%]	Customer  Assigment [%]
Supplier	Customer  Assigment [%]	Supplier	Supplier
Ariba Id	Supplier	Ariba Id	Ariba Id
Industry	Ariba Id	Industry	Industry
Local Initiative	Industry	Local Initiative	Local Initiative
Local Flagging	Local Initiative	Local Flagging	Local Flagging
Comments	Local Flagging	Comments	Comments
Degree of Implementation	Degree of Implementation	Degree of Implementation	Degree of Implementation
Action Description	Action Description	Action Description	Action Description
Currency	Currency	Currency	Currency
Jan A (Sav)	Jan A (Sav)	Jan A (Sav)	Jan A (Sav)
Feb A (Sav)	Feb A (Sav)	Feb A (Sav)	Feb A (Sav)
Mar A (Sav)	Mar A (Sav)	Mar A (Sav)	Mar A (Sav)
Apr A (Sav)	Apr A (Sav)	Apr A (Sav)	Apr A (Sav)
May A (Sav)	May A (Sav)	May A (Sav)	May A (Sav)
Jun A (Sav)	Jun A (Sav)	Jun A (Sav)	Jun A (Sav)
Jul A (Sav)	Jul A (Sav)	Jul A (Sav)	Jul A (Sav)
Aug A (Sav)	Aug A (Sav)	Aug A (Sav)	Aug A (Sav)
Sep E (Sav)	Sep A (Sav)	Sep A (Sav)	Sep A (Sav)
Oct E (Sav)	Oct E (Sav)	Oct A (Sav)	Oct E (Sav)
Nov E (Sav)	Nov E (Sav)	Nov E (Sav)	Nov E (Sav)
Dec E (Sav)	Dec E (Sav)	Dec E (Sav)	Dec E (Sav)
(Sav)	Sep A -Sep E (Sav)	Oct A - Oct E (Sav)	Sep A -Sep E (Sav)
Q1 (Sav)	Q1 (Sav)	Q1 (Sav)	Q1 (Sav)
Q2 (Sav)	Q2 (Sav)	Q2 (Sav)	Q2 (Sav)
Q3 (Sav)	Q3 (Sav)	Q3 (Sav)	Q3 (Sav)
Q4 (Sav)	Q4 (Sav)	Q4 (Sav)	Q4 (Sav)
H1 (Sav)	H1 (Sav)	H1 (Sav)	H1 (Sav)
H2 (Sav)	H2 (Sav)	H2 (Sav)	H2 (Sav)
FY Total (Sav)	FY Total (Sav)	FY Total (Sav)	FY Total (Sav)
YTD (Sav)	YTD (Sav)	YTD (Sav)	YTD (Sav)
Jan (Sav) W	Oct (Sav) W	Nov (Sav) W	Oct (Sav) W
Feb (Sav) W	Nov (Sav) W	Dec (Sav) W	Nov (Sav) W
Mar (Sav) W	Dec (Sav) W	Q4 (Sav) W	Dec (Sav) W
Apr (Sav) W	Q4 (Sav) W	H2 (Sav) W	Q4 (Sav) W
May (Sav) W	H2 (Sav) W	Jan A (HC)	H2 (Sav) W
Jun (Sav) W	Jan A (HC)	Feb A (HC)	Jan A (HC)
Jul (Sav) W	Feb A (HC)	Mar A (HC)	Feb A (HC)
Aug (Sav) W	Mar A (HC)	Apr A (HC)	Mar A (HC)
Sep (Sav) W	Apr A (HC)	May A (HC)	Apr A (HC)
Oct (Sav) W	May A (HC)	Jun A (HC)	May A (HC)
Nov (Sav) W	Jun A (HC)	Jul A (HC)	Jun A (HC)
Dec (Sav) W	Jul A (HC)	Aug A (HC)	Jul A (HC)
Q1 (Sav) W	Aug A (HC)	Sep A (HC)	Aug A (HC)
Q2 (Sav) W	Sep A (HC)	Oct A (HC)	Sep A (HC)
Q3 (Sav) W	Oct E (HC)	Nov E (HC)	Oct E (HC)
Q4 (Sav) W	Nov E (HC)	Dec E (HC)	Nov E (HC)
H1 (Sav) W	Dec E (HC)	Oct A - Oct E (HC)	Dec E (HC)
H2 (Sav) W	Sep A -Sep E (HC)	Q1 (HC)	Sep A -Sep E (HC)
Jan A (HC)	Q1 (HC)	Q2 (HC)	Q1 (HC)
FAb A (HC)	Q2 (HC)	Q3 (HC)	Q2 (HC)
Mar A (HC)	Q3 (HC)	Q4 (HC)	Q3 (HC)
Apr A (HC)	Q4 (HC)	H1 (HC)	Q4 (HC)
May A (HC)	H1 (HC)	H2 (HC)	H1 (HC)
Jun A (HC)	H2 (HC)	FY (HC)	H2 (HC)
Jul A (HC)	FY (HC)	YTD (HC)	FY (HC)
Aug A (HC)	YTD (HC)	Item #	YTD (HC)
Sep E (HC)	Item #	Rally Id	Item #
Oct E (HC)	Rally Id	Deployment ID	Rally Id
Nov E (HC)	Deployment ID	Last Update User	Deployment ID
Dec E (HC)	Last Update User	Project Id	Last Update User
(HC)	Project Id	In Outlook	Project Id
Q1 (HC)	In Outlook	Acquisition	In Outlook
Q2 (HC)	Acquisition		Acquisition
Q3 (HC)
Q4 (HC)
H1 (HC)
H2 (HC)
FY (HC)
YTD (HC)
Item #
Rally Id
Deployment ID
Last Update User
Project Id
In Outlook
Acquisition

[BA_Pete]

Hi @darylmitchell ,

When I asked for your full M query, I meant the M code from Advanced Editor in Desktop. Sorry, I should have been clearer with what I needed.

Also, exactly what error are you getting when you try to refresh in the Service? I'm assuming you're getting your Excel data from SharePoint or Azure, or that you've got an Enterprise Gateway set up?

Pete

[darylmitchell]

The data is coming from Onedrive for business and is in xlsx format.

This is what was failing when a column isn't available.
But as you will see I do not references any columns specifically.

let

  Source = Excel.Workbook(Web.Contents("https://domain/Documents/HCI%20Management%20Dashboards/Supporting%20Data/DIARIE/Cache_Action_Line_It..."), null, true),

  Cache_Action_Line_Item_Details_Sheet = Source{[Item = "Cache_Action_Line_Item_Details", Kind = "Sheet"]}[Data],

  #"Removed Top Rows" = Table.Skip(Cache_Action_Line_Item_Details_Sheet, 24),

  #"Promoted Headers" = Table.PromoteHeaders(#"Removed Top Rows", [PromoteAllScalars = true]),

  Custom = Table.TransformColumnTypes(#"Promoted Headers",List.Transform(Table.ColumnNames(#"Promoted Headers"), each {_ , type text})),

  #"Removed Columns" = Table.RemoveColumns(Custom, {"Column1"})

in

  #"Removed Columns"

Type of error I get.   "Error: Expression.Error: The column '(HC)_1' of the table wasn't found. <ccon>(HC)_1</ccon>. RootActivityId = c01c7c64-523a-4106-b63d-27ddfb7ee3ee.Param1 = Expression.Error: The column '(HC)_1' of the table wasn't found. <ccon>(HC)_1</ccon> Request ID: a28ad399-2c04-441b-92e9-34467c386bc7."

[BA_Pete]

Ok. I can't see the unpivot step that you referenced in your original post in this code. I think this is potentially a key step here as this obviously removes a lot of the column name values issue as they become row values instead. Just make sure to use 'Unpivot OTHER Columns' after selecting known and non-changing columns first.

Another option might be to create a dummy table that just contains all variations of the changeable column names with empty values. You then append this dummy onto your actual data as soon as it comes in and it will give you a table with all possible column names, allowing hard-referenced transformations to always work. Not ideal, but an option.

Beyond this, I think I'd need to see an example of anonymised raw data and an example of your expected output to really be able to put together a bespoke solution.

Pete

[j_ocean]

I see the days have different columns. Make sure your code isn't naming specific columns to unpivot, if it is add a dummy column and use "unpivot other columns".

[darylmitchell]

I have tried the unpivot.
But it fails reading the inital data, before I get to the step to unpivot.
This only hapopend when I move a pibx to Enterprise, not when I run locally on desktop.

[j_ocean]

In that case try this: In desktop Power Query, click the little square between the column and row headers and select "keep errors". Sometimes Desktop can handle individual cell errors in a way Service can't.

[darylmitchell]

"kept errors" seems to remove the rows that are erroring,  which because it is a column issue it relates to all rows.  So the flow runs but I get no data now.
Thanks for trying.