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.
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.
SergeiBaklan This helped me also, at least a bit. I'm working the same data but just thru Excel365/Query. Within the query removing the Columns =189 and changing to Columns = null worked great inside of the query application. But I'm having some trouble working in the rest of your code.
How do I fit everything in after that?
- SergeiBaklanJul 21, 2020Diamond Contributor
You shall delete the step on you second screenshot (automatically generated Change Type) and use Change Type from the code instead.