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

[Anonymous]

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?   

[ryan_mayu]

@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 · Accepted solution]

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

[ryan_mayu]

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