Forum Discussion

Caesarus7's avatar
Caesarus7
Copper Contributor
Feb 25, 2020
Solved

Refresh query to add new columns

Hi all,   I'm looking to track the Coronavirus and update daily so I can see how many new cases there are.  I am pulling the information from http://CSSEGISandData/COVID-19/blob/master/csse_covid_...
  • SergeiBaklan's avatar
    Mar 10, 2020

    Caesarus7 

    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.

Resources