Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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
Anonymous
Not applicable

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
Super User
Super User

@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

 

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Anonymous
Not applicable

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}})

@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.





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.