Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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?
Solved! Go to 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}})
@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
Proud to be a Super User!
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.
Proud to be a Super User!
User | Count |
---|---|
125 | |
106 | |
99 | |
63 | |
62 |
User | Count |
---|---|
135 | |
116 | |
101 | |
71 | |
61 |