Power Query function stopped working after months...getting Error msg

SOQLee

Board Regular
Joined
Mar 18, 2015
Messages
58
Office Version
  1. 365
Platform
  1. Windows
I have been refreshing the data files in the workbook on a weekly basis for months with no issues until now. Today, the function stops working and I am getting an error when I replace the files which are exported from our system each week.

I've checked through last week and this week files and I see not changes in the layout or naming convention of the exported data. (ie. no change to row/header layout; no columns added/deleted; same naming convention, etc.)

I've checked the function and there are no scripting errors. The function removes top row; and promotes row2 as header. See below screenshot for a copy of the script.

I don't understand why function has stopped working on system files from one week to the next. I have no idea on how to troubleshoot this. Any advice appreciated.




1649702144067.png




= (filepath)=>
let
Source = Excel.Workbook(File.Contents(filepath)),
sheet1_Sheet = Source{[Item="sheet1",Kind="Sheet"]}[Data],
#"Changed Type" = Table.TransformColumnTypes(sheet1_Sheet,{{"Column1", type text}, {"Column2", type text}, {"Column3", type any}, {"Column4", type text}, {"Column5", type any}, {"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 any}, {"Column17", type text}, {"Column18", type text}, {"Column19", type text}, {"Column20", type text}, {"Column21", type text}, {"Column22", type text}, {"Column23", type text}, {"Column24", type text}, {"Column25", type text}, {"Column26", type text}, {"Column27", type text}, {"Column28", type text}, {"Column29", type text}, {"Column30", type text}, {"Column31", type text}, {"Column32", type text}, {"Column33", type any}, {"Column34", type text}, {"Column35", type text}, {"Column36", type text}, {"Column37", type text}, {"Column38", type text}, {"Column39", type text}, {"Column40", type text}, {"Column41", type text}}),
#"Removed Top Rows" = Table.Skip(#"Changed Type",1),
#"Promoted Headers" = Table.PromoteHeaders(#"Removed Top Rows", [PromoteAllScalars=true]),

#"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"ID", Int64.Type}, {"Career", type text}, {"Career Nbr", Int64.Type}, {"Appl Nbr", Int64.Type}, {"Prog Nbr", Int64.Type}, {"Acad Prog", type text}, {"AreaOfSpecialization", type text}, {"Option", type text}, {"Prog Actn", type text}, {"Action Rsn", Int64.Type}, {"Admit Term", Int64.Type}, {"Prefix", type text}, {"Last", type text}, {"First Name", type text}, {"Sex", type text}, {"Birthdate", Int64.Type}, {"HAddr1", type text}, {"HAddr2", type text}, {"HAddr3", type text}, {"HAddr4", type text}, {"HCity", type text}, {"HProv", type text}, {"HPostalCd", type text}, {"HCountry", type text}, {"MAddr1", type text}, {"MAddr2", type text}, {"MAddr3", type text}, {"MAddr4", type text}, {"MCity", type text}, {"MState", type text}, {"MPostalCd", type text}, {"MCountry", type text}, {"ResEntryDate", Int64.Type}, {"ResStatus", type text}, {"ResCountry", type text}, {"PrefPhone", type text}, {"CellPhone", type text}, {"Email", type text}, {"Email_1", type text}, {"Admit Type", type text}, {"Choice", Int64.Type}})
in
#"Changed Type1"
 

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)
UPDATE...well I got around the 'error' by defining a 'Table1' in the 3 files I wanted to import into Power Query.

I can only think that the exported system files now contain hidden properties; and going forward not as simple as downloading the files and pointing PQ to the source folder. For 3 files, it's a minor pain.
 
Upvote 0

Forum statistics

Threads
1,223,896
Messages
6,175,263
Members
452,627
Latest member
KitkatToby

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top