Forum Discussion
Refresh query to add new columns
- Mar 10, 2020
To work with columns dynamically you shall to change Columns parameter to null. After that depends what are you doing here, in this script change type for the dynamic list of columns.
let //Source= Csv.Document(File.Contents("C:\Test\time_series_19-covid-Confirmed.csv"),[Delimiter=",", Columns=38, Encoding=1252, QuoteStyle=QuoteStyle.None]) // Change in above Columns=38 (or whatever number you have) on // Columns=null - all existing columns will be taken automatically Source = Csv.Document( File.Contents("C:\Test\time_series_19-covid-Confirmed.csv"), [Delimiter=",", Columns=null, Encoding=1252, QuoteStyle=QuoteStyle.None] ), #"Promoted Headers" = Table.PromoteHeaders( Source, [PromoteAllScalars=true] ), //Here we dynamically change columns type on Whole Number //for all columns starting from 5th one (4+1) // Before that remove automatically added #"Changed Type" step #"Changed Type"=Table.TransformColumnTypes( #"Promoted Headers", List.Transform( List.Range( Table.ColumnNames(#"Promoted Headers"),4 ), each {_, Int64.Type} ) ), // Additionally apply proper type to first coluns, // they are always the same #"Changed Type1" = Table.TransformColumnTypes( #"Changed Type", { {"Lat", type number}, {"Long", type number} } ) in #"Changed Type1"
Same script is in attached file, you only shall to change the source to make it workable.
Hope that helps
Lewis-H I honestly can't find where the query editor is even located to be able to go into it to refresh anything. I love how everyone says go here, but don't bother to tell you where it can be found. I know your comment is 2 years old but this is so frustrating!
- Leon TribeMar 10, 2022MVP
The short answer is go to the Data tab in Excel, the longer answer is described through the following link.
- patidallas22Mar 10, 2022Copper ContributorI'm not that advanced of an Excel or Power BI user. I would have no clue how to even edit the query script that Sergei included above. I was hoping there was a much simpler option to get Power BI to recognize new columns were added. For me it was simpler to just start over, although not preferred.
- Ed_K3Mar 09, 2022Copper Contributor
patidallas22 Are you working in Power BI or Excel?
- patidallas22Mar 10, 2022Copper Contributor
Ed_K3 I was working in Power BI. I pulled data from Excel, but then I added a couple of more columns in Excel, but the Power BI after refreshing did not add those two new columns. I read where people said to do the query, but I couldn't find that in Power BI. I just would have preferred some additional steps on how to get there.