Appending Excel in Power Query Error

[Anonymous]

Hi everyone,

I have a power query that appends different files together, these files are located on a SharePoint 2010 server. Every day a new file is uploaded. The appending them worked fine in the past.

Recently my query now gives me the following error:

An error occurred in the ‘Transform File (2)’ query. DataFormat.Error: The document cannot be opened because there is an invalid part with an unexpected content type.
[Part Uri=/xl/worksheets/sheet2.xml],
[Content Type=application/xml],
[Expected Content Type=application/vnd.openxmlformats-officedocument.spreadsheetml.worksheet+xml].
Details:
[Binary]

This issue started occuring when the files started being automatically dropped there. The files are identical in nature, there are no data errors with it. When the file that shows an error is open and then closed, it resolves itself, no changes were made to the file. I don't know what the issue could be, have looked everywhere. I don't think it's a feasible solution to open and close the new file every day.

Edit: Added fact: I have another query doing something similar and it has no issue, the only difference between the two queries is that the source file for the one that doesn't work is automatically outputted and automatically added to SharePoint 2010 whereas the other one is manually added to SharePoint 2010. 

Any help anyone can provide would be a great help.

Thanks in advance,

Van

[lbendlin]

Are these files .xls or .xlsx ?

[Anonymous]

Hi! Thank you for responding, they are .xlsx file types. 

[lbendlin]

Would you be able to post a sanitized sample of your Power Query code?

[Anonymous]

Hi Ibendlin, I'm the code is below:

let
Source = SharePoint.Files(SharePoint, [ApiVersion = 14]),
#"Filtered Rows" = Table.SelectRows(Source, each ([Folder Path] = "https://dialogue/grp/WG7393306/DashboardDatasets/CSGC Project Data/")),
#"Filtered Hidden Files1" = Table.SelectRows(#"Filtered Rows", each [Attributes]?[Hidden]? <> true),
#"Invoke Custom Function1" = Table.AddColumn(#"Filtered Hidden Files1", "Transform File (2)", each #"Transform File (2)"([Content])),
#"Renamed Columns1" = Table.RenameColumns(#"Invoke Custom Function1", {"Name", "Source.Name"}),
#"Removed Other Columns1" = Table.SelectColumns(#"Renamed Columns1",{"Source.Name", "Date created", "Transform File (2)"}),
#"Expanded Transform File" = Table.ExpandTableColumn(#"Removed Other Columns1", "Transform File (2)", {"project_nu", "region_id", "region", "sap_fund_code", "PROGRAM_ID", "prog_code", "prog_name", "prog_type", "cost_centre", "actprovid", "actproven", "orgprovid", "orgproven", "org_name", "legal_name", "business_number", "org_type", "org_category", "BANK_NAME_EN", "TRANSIT_NUMBER", "BANK_NUMBER", "ACCOUNT_NUMBER", "BRANCH_CIVIC_ADDRESS", "BANK_POSTAL_CODE", "mailing_city", "mailing_address", "mailing_postal", "act_city", "act_address_1", "act_address_2", "act_postal_code", "CFP_IDENTIFIER", "CFP_intake_start", "CFP_intake_end", "CSGC_intake_start", "CSGC_intake_end", "PROJECT_OFFICER_NAME", "Setup_INPROC", "Setup_REJECT", "Setup_WD", "Setup_CANC", "Setup_COMPL", "Asses_INPROC", "Asses_REJECT", "Asses_WD", "Asses_CANC", "Asses_COMPL", "RecAppr_INPROC", "RecAppr_REJECT", "RecAppr_WD", "RecAppr_APPR", "appl_source", "appl_date", "status_en", "ack_sent_date", "recom_date", "appr_date", "event_date", "project_st", "project_en", "CLOSE_OUT_DATE", "fund_req", "fund_appr", "fund_agree"}, {"project_nu", "region_id", "region", "sap_fund_code", "PROGRAM_ID", "prog_code", "prog_name", "prog_type", "cost_centre", "actprovid", "actproven", "orgprovid", "orgproven", "org_name", "legal_name", "business_number", "org_type", "org_category", "BANK_NAME_EN", "TRANSIT_NUMBER", "BANK_NUMBER", "ACCOUNT_NUMBER", "BRANCH_CIVIC_ADDRESS", "BANK_POSTAL_CODE", "mailing_city", "mailing_address", "mailing_postal", "act_city", "act_address_1", "act_address_2", "act_postal_code", "CFP_IDENTIFIER", "CFP_intake_start", "CFP_intake_end", "CSGC_intake_start", "CSGC_intake_end", "PROJECT_OFFICER_NAME", "Setup_INPROC", "Setup_REJECT", "Setup_WD", "Setup_CANC", "Setup_COMPL", "Asses_INPROC", "Asses_REJECT", "Asses_WD", "Asses_CANC", "Asses_COMPL", "RecAppr_INPROC", "RecAppr_REJECT", "RecAppr_WD", "RecAppr_APPR", "appl_source", "appl_date", "status_en", "ack_sent_date", "recom_date", "appr_date", "event_date", "project_st", "project_en", "CLOSE_OUT_DATE", "fund_req", "fund_appr", "fund_agree"}),
#"Removed Other Columns" = Table.SelectColumns(#"Expanded Transform File",{"Date created", "project_nu", "region_id", "region", "sap_fund_code", "PROGRAM_ID", "prog_code", "prog_name", "prog_type", "cost_centre", "actprovid", "actproven", "orgprovid", "orgproven", "org_name", "legal_name", "mailing_city", "mailing_address", "mailing_postal", "act_city", "act_address_1", "act_address_2", "act_postal_code", "CFP_IDENTIFIER", "CFP_intake_start", "CFP_intake_end", "CSGC_intake_start", "CSGC_intake_end", "PROJECT_OFFICER_NAME", "Setup_INPROC", "Setup_REJECT", "Setup_WD", "Setup_CANC", "Setup_COMPL", "Asses_INPROC", "Asses_REJECT", "Asses_WD", "Asses_CANC", "Asses_COMPL", "RecAppr_INPROC", "RecAppr_REJECT", "RecAppr_WD", "RecAppr_APPR", "appl_source", "appl_date", "status_en", "ack_sent_date", "recom_date", "appr_date", "event_date", "project_st", "project_en", "CLOSE_OUT_DATE", "fund_req", "fund_appr", "fund_agree"}),
#"Grouped Rows" = Table.Group(#"Expanded Transform File", {"project_nu"}, {{"MaxModDate", each Table.Max(_,"Date created")}}),
#"Expanded MaxModDate" = Table.ExpandRecordColumn(#"Grouped Rows", "MaxModDate", {"Source.Name", "Date created", "project_nu", "region_id", "region", "sap_fund_code", "PROGRAM_ID", "prog_code", "prog_name", "prog_type", "cost_centre", "actprovid", "actproven", "orgprovid", "orgproven", "org_name", "legal_name", "business_number", "org_type", "org_category", "BANK_NAME_EN", "TRANSIT_NUMBER", "BANK_NUMBER", "ACCOUNT_NUMBER", "BRANCH_CIVIC_ADDRESS", "BANK_POSTAL_CODE", "mailing_city", "mailing_address", "mailing_postal", "act_city", "act_address_1", "act_address_2", "act_postal_code", "CFP_IDENTIFIER", "CFP_intake_start", "CFP_intake_end", "CSGC_intake_start", "CSGC_intake_end", "PROJECT_OFFICER_NAME", "Setup_INPROC", "Setup_REJECT", "Setup_WD", "Setup_CANC", "Setup_COMPL", "Asses_INPROC", "Asses_REJECT", "Asses_WD", "Asses_CANC", "Asses_COMPL", "RecAppr_INPROC", "RecAppr_REJECT", "RecAppr_WD", "RecAppr_APPR", "appl_source", "appl_date", "status_en", "ack_sent_date", "recom_date", "appr_date", "event_date", "project_st", "project_en", "CLOSE_OUT_DATE", "fund_req", "fund_appr", "fund_agree"}, {"Source.Name", "Date created", "project_nu.1", "region_id", "region", "sap_fund_code", "PROGRAM_ID", "prog_code", "prog_name", "prog_type", "cost_centre", "actprovid", "actproven", "orgprovid", "orgproven", "org_name", "legal_name", "business_number", "org_type", "org_category", "BANK_NAME_EN", "TRANSIT_NUMBER", "BANK_NUMBER", "ACCOUNT_NUMBER", "BRANCH_CIVIC_ADDRESS", "BANK_POSTAL_CODE", "mailing_city", "mailing_address", "mailing_postal", "act_city", "act_address_1", "act_address_2", "act_postal_code", "CFP_IDENTIFIER", "CFP_intake_start", "CFP_intake_end", "CSGC_intake_start", "CSGC_intake_end", "PROJECT_OFFICER_NAME", "Setup_INPROC", "Setup_REJECT", "Setup_WD", "Setup_CANC", "Setup_COMPL", "Asses_INPROC", "Asses_REJECT", "Asses_WD", "Asses_CANC", "Asses_COMPL", "RecAppr_INPROC", "RecAppr_REJECT", "RecAppr_WD", "RecAppr_APPR", "appl_source", "appl_date", "status_en", "ack_sent_date", "recom_date", "appr_date", "event_date", "project_st", "project_en", "CLOSE_OUT_DATE", "fund_req", "fund_appr", "fund_agree"}),
#"Changed Type" = Table.TransformColumnTypes(#"Expanded MaxModDate",{{"region_id", Int64.Type}, {"region", type text}, {"PROGRAM_ID", Int64.Type}, {"prog_name", type text}, {"prog_type", type text}, {"cost_centre", Int64.Type}, {"actprovid", Int64.Type}, {"actproven", type text}, {"orgprovid", Int64.Type}, {"orgproven", type text}, {"Setup_INPROC", type date}, {"Setup_REJECT", type date}, {"Setup_WD", type date}, {"Setup_CANC", type date}, {"Setup_COMPL", type date}, {"Asses_INPROC", type date}, {"Asses_REJECT", type date}, {"Asses_WD", type date}, {"Asses_CANC", type date}, {"Asses_COMPL", type date}, {"RecAppr_INPROC", type date}, {"RecAppr_REJECT", type date}, {"RecAppr_WD", type date}, {"RecAppr_APPR", type date}, {"appl_source", type text}, {"status_en", type text}, {"appl_date", type date}, {"ack_sent_date", type date}, {"recom_date", type date}, {"appr_date", type date}, {"event_date", type date}, {"CLOSE_OUT_DATE", type date}, {"fund_req", Int64.Type}, {"fund_appr", Int64.Type}, {"fund_agree", Int64.Type}, {"project_nu", type text}, {"CFP_intake_start", type date}, {"CFP_intake_end", type date}, {"CSGC_intake_start", type date}, {"CSGC_intake_end", type date}, {"sap_fund_code", type text}}),
#"CSGC ID & Center" = Table.AddColumn(#"Changed Type", "CSGC ID & Center", each Text.From([PROGRAM_ID])

& " " &

Text.From([cost_centre])),
#"Intake Type" = Table.AddColumn(#"CSGC ID & Center", "Intake Type", each try

if Text.Length([CFP_IDENTIFIER]) > 4 then

Text.Start([CFP_IDENTIFIER], 3)

else null

otherwise

null),
#"AR Target Date" = Table.AddColumn(#"Intake Type", "Application Received Target Date", each if [CFP_intake_end] <> null and [CFP_intake_end] >= Date.FromText("2021-04-01") and [Intake Type] <> null then Date.AddDays([CFP_intake_end], 14) else
if [CFP_intake_end] <> null and [CFP_intake_end] < Date.FromText("2021-04-01") and [Intake Type] <> null then Date.AddDays([CFP_intake_end], 21) else

if [CSGC_intake_end] <> null and [CSGC_intake_end] >= Date.FromText("2021-04-01") then Date.AddDays([CSGC_intake_end], 14) and [Intake Type] <> null else
if [CSGC_intake_end] <> null and [CSGC_intake_end] < Date.FromText("2021-04-01") and [Intake Type] <> null then Date.AddDays([CSGC_intake_end], 21) else

null),
#"AR Fiscal Year" = Table.AddColumn(#"AR Target Date", "AR Fiscal Year", each if Date.Month([Application Received Target Date]) < 4
then
(Date.Year([Application Received Target Date])-1)
else
(Date.Year([Application Received Target Date]))),
#"PD Target Date" = Table.AddColumn(#"AR Fiscal Year", "Payment Date Target Date", each Date.AddDays([project_st], 14)),
#"PCO Target Date" = Table.AddColumn(#"PD Target Date", "Close Out Target Date", each try

if [PROGRAM_ID] = 324 then

Date.AddMonths([project_en], 4)

else

Date.AddMonths([project_en], 6)

otherwise null),
#"PCO Bin" = Table.AddColumn(#"PCO Target Date", "Close Out Bin", each try

if [Close Out Target Date] < [CLOSE_OUT_DATE] then 0 else 1

otherwise

0),
#"SS1 Bin" = Table.AddColumn(#"PCO Bin", "Service Standard 1 (Bin)", each try

if [ack_sent_date] = null then 0 else

if Duration.Days([ack_sent_date] - [Application Received Target Date]) <= 0 then 1 else 0

otherwise 0),
#"Assessment Days" = Table.AddColumn(#"SS1 Bin", "Days to Assessment", each try

Duration.Days([Asses_COMPL] - [appl_date])

otherwise null),
#"Recommendation Days" = Table.AddColumn(#"Assessment Days", "Days to Recommendation", each try

Duration.Days([recom_date] - [appl_date])

otherwise null),
#"Approval Days" = Table.AddColumn(#"Recommendation Days", "Days to Approval", each try

Duration.Days([appr_date] - [appl_date])

otherwise null),
#"Assessment Date" = Table.AddColumn(#"Approval Days", "Assessment Date", each try

if [Asses_COMPL] <> null then [Asses_COMPL] else
if [Asses_CANC] <> null then [Asses_CANC] else
if [Asses_WD] <> null then [Asses_WD] else
if [Asses_REJECT] <> null then [Asses_REJECT] else
null

otherwise

null),
#"Fund Code - CSGC - Cost Center" = Table.AddColumn(#"Assessment Date", "Fund Code - CSGC ID - Cost Center", each try

Text.Combine({Text.From([sap_fund_code]), Text.From([PROGRAM_ID]), Text.From([cost_centre])}, " ")

otherwise

null),
#"CSGC ID & Cost Center" = Table.AddColumn(#"Fund Code - CSGC - Cost Center", "CSGC ID & Cost Center", each Text.From([PROGRAM_ID]) & " " & Text.From([cost_centre])),
Funded = Table.AddColumn(#"CSGC ID & Cost Center", "Project Funded", each try

if [fund_agree] > 0 then "TRUE" else "FALSE"

otherwise "FALSE"),
#"Days to Acknowledge" = Table.AddColumn(Funded, "Days to Acknowledge", each try

Duration.Days([ack_sent_date] - [appl_date])

otherwise null),
#"Days to Setup" = Table.AddColumn(#"Days to Acknowledge", "Days to Setup", each try

Duration.Days([Setup_COMPL] - [appl_date])

otherwise null),
#"Measurement Date" = Table.AddColumn(#"Days to Setup", "Measurement Date", each if [CSGC_intake_end] = null then

if [CFP_intake_end] = null then [appl_date] else [CFP_intake_end]

else [CSGC_intake_end]),
#"New Fields Type" = Table.TransformColumnTypes(#"Measurement Date",{{"region_id", Int64.Type}, {"project_st", type date}})

in
#"New Fields Type"

The issue happens between the #"Expanded Transform file" and #"Removed Other Columns" and #"Grouped Rows"

Thanks,