• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

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

Lance Pretorius

New Member
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/20191 - 3031 - 6061 - 9091 - 120OlderTotal
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

Excel Ninja
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.
 

Chihiro

Excel Ninja
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?
 

GraH - Guido

Well-Known Member
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.
Code:
= 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.
 

Attachments

Last edited:
Top