cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

Expression.Error: The column 'XXX' of the table wasn't found.

My data source is from excel file.

My data is branch in the first column such as Camberwell, Bundoora, Burwood, Sunshine etc and the months run across on the columns from Aug 2017 to Oct 2018. 

I loaded the excel tab to power bi desktop, and I edit queries and unpivoted other columns. The visuals was working well.

 

When I add Nov 2018 data as a new column to the excel file and referesh power bi desktop report, I have got Expression.Error The column 'Column 17' of the table was't  found.

 

Can you advise how to add new data in the source file so the refresh in Power BI will work please?   

1 ACCEPTED SOLUTION

Accepted Solutions
Anonymous
Not applicable

Re: Expression.Error: The column 'XXX' of the table wasn't found.

Thanks Ryan for your advice.

 

I modify the query to below. Does it mean that I have to modify the queries everytime I add new data in a new month?

 

= Table.TransformColumnTypes(#"Promoted Headers",{{"Offices", type text}, {"1/08/2017", type number}, {"1/09/2017", type number}, {"1/10/2017", type number}, {"1/11/2017", type number}, {"1/12/2017", type number}, {"1/01/2018", type number}, {"1/02/2018", type number}, {"1/03/2018", type number}, {"1/04/2018", type number}, {"1/05/2018", type number}, {"1/06/2018", type number}, {"1/07/2018", type number}, {"1/08/2018", type number}, {"1/09/2018", type number}, {"1/10/2018", type number}, {"1/11/2018", type number}})

View solution in original post

3 REPLIES 3
ryan_mayu Established Member
Established Member

Re: Expression.Error: The column 'XXX' of the table wasn't found.

@Anonymous

 

You can go to Edit Quiries. In the Power Query Editor view, right click the sheet and choose "Advanced Editor" and add the new column  info in the coding.

 

let
    Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(Sheet1_Sheet, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"date", Int64.Type}, {new  column}})
in
    #"Changed Type"

 

Thanks and BR

Ryan

 

 

Anonymous
Not applicable

Re: Expression.Error: The column 'XXX' of the table wasn't found.

Thanks Ryan for your advice.

 

I modify the query to below. Does it mean that I have to modify the queries everytime I add new data in a new month?

 

= Table.TransformColumnTypes(#"Promoted Headers",{{"Offices", type text}, {"1/08/2017", type number}, {"1/09/2017", type number}, {"1/10/2017", type number}, {"1/11/2017", type number}, {"1/12/2017", type number}, {"1/01/2018", type number}, {"1/02/2018", type number}, {"1/03/2018", type number}, {"1/04/2018", type number}, {"1/05/2018", type number}, {"1/06/2018", type number}, {"1/07/2018", type number}, {"1/08/2018", type number}, {"1/09/2018", type number}, {"1/10/2018", type number}, {"1/11/2018", type number}})

View solution in original post

ryan_mayu Established Member
Established Member

Re: Expression.Error: The column 'XXX' of the table wasn't found.

@Anonymous

 

If you want to do data analysis in PBI, you'd better put all data in one column. Otherwise, you need to do the data transforming before creating a mesure.

Helpful resources

Announcements
New Kudos Received Badges Coming

New Kudos Received Badges Coming

Kudos to you if you earned one of these! Check your inbox for a notification.

Microsoft Implementation for Communities Wins Award

Microsoft Implementation for Communities Wins Award

Learn about the award-winning innovation that was implemented across Microsoft’s Business Applications Communities.

Power Platform World Tour

Power Platform World Tour

Find out where you can attend!

Top Kudoed Authors (Last 30 Days)
Users online (1,242)