Power Query Error: The column 'xxx' of the table wasn't found

[Lance Pretorius]

Hi
Every week I import an accounts receivable list in to power query. See below.
The problem I have is the second column header. Every time I run the report on a new date I get the "The column 'xxx' of the table wasn't found" error, because the date changed. How do I fix this problem?

Thanks in advance.

	Not due on 04/24/2019	1 - 30	31 - 60	61 - 90	91 - 120	Older	Total
Customer 1	25.00	60.00	-	-	10.00	-	95.00
Customer 2	-	5.00	4.00	18.00			27.00
Customer 3	10.00	5.00					15.00
	35.00	70.00	4.00	18.00	10.00	-	137.00

 

[Chihiro]

Upload sample workbook with raw data and expected output. And information on which direction the source table grows.

I'd imagine, it's due to hard coded column header value that's causing issue.

You may need to code it in Advanced Editor using "M" language. Or add custom function in "M" to handle it.

 

[Lance Pretorius]

I have attached 2 files.
DownLoadFile contains the source data and ReportFile has the power query

 

[Chihiro]

Ok, when the new data is imported/added. At which step does the query fail?

Is it at "Reordered Columns" or at "Reordered Columns1" part?

 

[Lance Pretorius]

To replicate the error change the value in Cell B1 of the down load file from "Not due on 04/30/2019" to "Not due on 04/29/2019"

 

[GraH - Guido]

Hi,

To replicate the error change the value in Cell B1 of the down load file from "Not due on 04/30/2019" to "Not due on 04/29/2019"

So you would have this as table headers "04/29/2019" "1 - 30" "31 - 60" "61 - 90" "91 - 120" "Older" "Total"
See what's wrong with this line of code, as Chihiro already suggested.

= Table.ReorderColumns(#"Added Index",{"Index", "Customer", " 04/30/2019", "1 - 30", "31 - 60", "61 - 90", "91 - 120", "Older", "Total", "Custom"})

To avoid it you can add steps/other query to first read the header record and use that as a list of "dynamic" titles.

 

[Lance Pretorius]

Thanks