Data query connection

[tcourter]

Hello,

I've recently started working with data queries and I've run into a little bit of a problem that I hoped to get some advice on.

Context:

• I've got a "master" sheet where all the data I'm querying needs to be aggregated to.
• There are around 10 worksheets which I'm querying data from, using Get Data > From File > From Excel Workbook.
• All 10 worksheets are updated once every 10 minutes by pulling data out of a remote database. The database software takes the most current data, puts it into an xlsx file, attaches it to an email, and sends it to my service account.
• I have a Power Automate cloud flow that saves the email-attached xlsx into a OneDrive folder. Each time it does this, it overwrites the original file so that the file name remains the same, but the data held within updates.

The problem I'm running into is that when the data in the 10 worksheets update, the information in the top row changes, as the date/time updates in the sheet. When this information changes, the data connection breaks because “[Expression.Error] The column ‘…’ of the table wasn’t found” (Screenshot attached). I’ve tried using the Transform Data option to exclude the top row, but I must be doing it wrong somehow because it still pops the same error message.

My goal is to get this data to aggregate in the "master" sheet without using workbook links, as I want to data to update automatically without having to open the "master" sheet every 10 minutes to update the workbook links.

Is there a better way of doing this that I'm not yet aware of?

 

[alansidman]

Please supply your Mcode for getting the source data and showing how you attempt to remove the top row.

 

[tcourter]

Please supply your Mcode for getting the source data and showing how you attempt to remove the top row.

Source:
= Excel.Workbook(File.Contents("C:\Users\FILEPATHFORSOURCESHEET.xlsx"), null, true)

Navigation:
= Source{[Item="Dept. Sales by Store DAILY",Kind="Sheet"]}[Data]

Promoted headers:
= Table.PromoteHeaders(#"Dept. Sales by Store DAILY_Sheet", [PromoteAllScalars=true])

Changed Type:
= Table.TransformColumnTypes(#"Promoted Headers",{{"...TODAY (01/22/2024 to 01/22/2024 23:59:59)...", type text}, {"Column2", type any}, {"Column3", type any}, {"Report Date: January 22, 2024 1:45 PM", type any}, {"Column5", type any}, {"Column6", type any}})

Removed Top Rows:
= Table.Skip(#"Changed Type",1)

The bolded step above is one of the two different ways I attempted to remove the top row from the query.

Another way I attempted to do this was by unchecking the "Use First Row as Headers" option:
Changed Type1:
= Table.TransformColumnTypes(#"Promoted Headers1",{{"Column1", type text}, {"Column2", type any}, {"Column3", type any}, {"Column4", type any}, {"Column5", type any}, {"Column6", type any}})

I tried those options both separately and together and it gives the same error either way.

 

[alansidman]

I typically use the Removed Top Rows that you show without issue. Not sure why this is not working for you. Hopefully, someone with a bit more understanding of Mcode will jump in here.

 

[tcourter]

I typically use the Removed Top Rows that you show without issue. Not sure why this is not working for you. Hopefully, someone with a bit more understanding of Mcode will jump in here.

I figured it out. I had to move "Removed Top Rows" above Promoted Headers in the order of Applied Steps. Screenshot attached.