SOLVED

Refresh query to add new columns

%3CLINGO-SUB%20id%3D%22lingo-sub-1192306%22%20slang%3D%22en-US%22%3ERefresh%20query%20to%20add%20new%20columns%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1192306%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20all%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI'm%20looking%20to%20track%20the%20Coronavirus%20and%20update%20daily%20so%20I%20can%20see%20how%20many%20new%20cases%20there%20are.%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20pulling%20the%20information%20from%20%3CA%20href%3D%22http%3A%2F%2FCSSEGISandData%2FCOVID-19%2Fblob%2Fmaster%2Fcsse_covid_19_data%2Fcsse_covid_19_time_series%2Ftime_series_19-covid-Confirmed.csv%22%20target%3D%22_self%22%20rel%3D%22nofollow%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3Egithub%3C%2FA%3E.%26nbsp%3B%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAs%20you%20can%20see%2C%20each%20day%20new%20information%20is%20%3CSTRONG%3Eadded%20to%20a%20new%20column%3C%2FSTRONG%3E.%3C%2FP%3E%3CP%3EMy%20query%20is%20capturing%20new%20rows%20for%20new%20cases%20in%20different%20regions%2C%20but%20not%20the%20new%20columns%20for%20each%20new%20day's%20data.%26nbsp%3B%3C%2FP%3E%3CP%3ECan%20someone%20assist%20to%20advise%20how%20I%20can%20update%20the%20query%20to%20capture%20the%20new%20columns%3F%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ERegards%3C%2FP%3E%3CP%3EJoel%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1192306%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1220756%22%20slang%3D%22en-US%22%3ERe%3A%20Refresh%20query%20to%20add%20new%20columns%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1220756%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20exactly%20the%20same%20problem.%20The%20best%20I%20can%20do%20is%3A%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ERefresh%20the%20query%20in%20Excel%20to%20flush%20the%20cache%3CBR%20%2F%3EOpen%20a%20blank%20Excel%3C%2FP%3E%0A%3CP%3EOpen%20the%20Raw%20link%20in%20the%20blank%20Excel%3C%2FP%3E%0A%3CP%3ECopy%20the%20query%20from%20Advanced%20Editor%3C%2FP%3E%0A%3CP%3EGo%20to%20my%20original%20Excel%2C%20go%20to%20Advanced%20Editor%20and%20copy%20in%20the%20new%20query%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIts%20a%20hassle%20and%20I%20am%20tempted%20to%20forget%20the%20query%20route%20altogether%20and%20just%20copy%20paste%20from%20the%20web%20site%20and%20use%20Text%20to%20Columns.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1220943%22%20slang%3D%22en-US%22%3ERe%3A%20Refresh%20query%20to%20add%20new%20columns%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1220943%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F262240%22%20target%3D%22_blank%22%3E%40Caesarus7%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ETo%20work%20with%20columns%20dynamically%20you%20shall%20to%20change%20Columns%20parameter%20to%20null.%20After%20that%20depends%20what%20are%20you%20doing%20here%2C%20in%20this%20script%20change%20type%20for%20the%20dynamic%20list%20of%20columns.%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3Elet%0A%20%20%20%20%2F%2FSource%3D%20Csv.Document(File.Contents(%22C%3A%5CTest%5Ctime_series_19-covid-Confirmed.csv%22)%2C%5BDelimiter%3D%22%2C%22%2C%20Columns%3D38%2C%20Encoding%3D1252%2C%20QuoteStyle%3DQuoteStyle.None%5D)%0A%20%20%20%20%2F%2F%20Change%20in%20above%20Columns%3D38%20(or%20whatever%20number%20you%20have)%20on%0A%20%20%20%20%2F%2F%20Columns%3Dnull%20%20-%20all%20existing%20columns%20will%20be%20taken%20automatically%20%20%20%20%0A%20%20%20%20Source%20%3D%20Csv.Document(%0A%20%20%20%20%20%20%20%20File.Contents(%22C%3A%5CTest%5Ctime_series_19-covid-Confirmed.csv%22)%2C%0A%20%20%20%20%20%20%20%20%5BDelimiter%3D%22%2C%22%2C%20Columns%3Dnull%2C%20Encoding%3D1252%2C%20QuoteStyle%3DQuoteStyle.None%5D%0A%20%20%20%20)%2C%0A%0A%20%20%20%20%23%22Promoted%20Headers%22%20%3D%20Table.PromoteHeaders(%0A%20%20%20%20%20%20%20%20Source%2C%0A%20%20%20%20%20%20%20%20%5BPromoteAllScalars%3Dtrue%5D%0A%20%20%20%20)%2C%0A%0A%20%20%20%20%2F%2FHere%20we%20dynamically%20change%20columns%20type%20on%20Whole%20Number%0A%20%20%20%20%2F%2Ffor%20all%20columns%20starting%20from%205th%20one%20(4%2B1)%0A%20%20%20%20%2F%2F%20Before%20that%20remove%20automatically%20added%20%23%22Changed%20Type%22%20step%0A%20%20%20%20%23%22Changed%20Type%22%3DTable.TransformColumnTypes(%0A%20%20%20%20%20%20%20%20%23%22Promoted%20Headers%22%2C%0A%20%20%20%20%20%20%20%20List.Transform(%0A%20%20%20%20%20%20%20%20%20%20%20%20List.Range(%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20Table.ColumnNames(%23%22Promoted%20Headers%22)%2C4%0A%20%20%20%20%20%20%20%20%20%20%20%20)%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20each%20%7B_%2C%20Int64.Type%7D%0A%20%20%20%20%20%20%20%20)%0A%20%20%20%20)%2C%0A%20%20%20%20%2F%2F%20Additionally%20apply%20proper%20type%20to%20first%20coluns%2C%0A%20%20%20%20%2F%2F%20they%20are%20always%20the%20same%0A%20%20%20%20%23%22Changed%20Type1%22%20%3D%20Table.TransformColumnTypes(%0A%20%20%20%20%20%20%20%20%23%22Changed%20Type%22%2C%0A%20%20%20%20%20%20%20%20%7B%0A%20%20%20%20%20%20%20%20%20%20%20%20%7B%22Lat%22%2C%20type%20number%7D%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20%7B%22Long%22%2C%20type%20number%7D%0A%20%20%20%20%20%20%20%20%7D%0A%20%20%20%20)%0Ain%0A%20%20%20%20%23%22Changed%20Type1%22%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3ESame%20script%20is%20in%20attached%20file%2C%20you%20only%20shall%20to%20change%20the%20source%20to%20make%20it%20workable.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1221292%22%20slang%3D%22en-US%22%3ERe%3A%20Refresh%20query%20to%20add%20new%20columns%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1221292%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3Bthank%20you%20so%20much!%20this%20is%20exactly%20what%20i%20was%20looking%20for.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ERegards%3C%2FP%3E%3CP%3EJoel%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1221762%22%20slang%3D%22en-US%22%3ERe%3A%20Refresh%20query%20to%20add%20new%20columns%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1221762%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F262240%22%20target%3D%22_blank%22%3E%40Caesarus7%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EJoel%2C%20glad%20to%20help%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1221859%22%20slang%3D%22en-US%22%3ERe%3A%20Refresh%20query%20to%20add%20new%20columns%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1221859%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3BYou%20have%20saved%20me%20a%20serious%20amount%20of%20time.%20Much%20appreciated.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F262240%22%20target%3D%22_blank%22%3E%40Caesarus7%3C%2FA%3E%26nbsp%3BIf%20you%20swap%20the%20file%20reference%20to%20this%3A%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3EWeb.Contents(%22https%3A%2F%2Fraw.githubusercontent.com%2FCSSEGISandData%2FCOVID-19%2Fmaster%2Fcsse_covid_19_data%2Fcsse_covid_19_time_series%2Ftime_series_19-covid-Confirmed.csv%22)%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3Eit%20will%20update%20online%20without%20having%20to%20download%20the%20csv.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1535729%22%20slang%3D%22en-US%22%3ERe%3A%20Refresh%20query%20to%20add%20new%20columns%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1535729%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3BThis%20helped%20me%20also%2C%20at%20least%20a%20bit.%26nbsp%3B%20I'm%20working%20the%20same%20data%20but%20just%20thru%20Excel365%2FQuery.%26nbsp%3B%20Within%20the%20query%20removing%20the%20Columns%20%3D189%20and%20changing%20to%20Columns%20%3D%20null%20worked%20great%20inside%20of%20the%20query%20application.%26nbsp%3B%20But%20I'm%20having%20some%20trouble%20working%20in%20the%20rest%20of%20your%20code.%3CBR%20%2F%3E%3CBR%20%2F%3E%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Ed_K3_1-1595338885821.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F206830i2EA0F5D9FB1BFBB5%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20title%3D%22Ed_K3_1-1595338885821.png%22%20alt%3D%22Ed_K3_1-1595338885821.png%22%20%2F%3E%3C%2FSPAN%3E%3CBR%20%2F%3EHow%20do%20I%20fit%20everything%20in%20after%20that%3F%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Ed_K3_2-1595339092640.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F206831iC0FF75BFA23F2B4F%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20title%3D%22Ed_K3_2-1595339092640.png%22%20alt%3D%22Ed_K3_2-1595339092640.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1535791%22%20slang%3D%22en-US%22%3ERe%3A%20Refresh%20query%20to%20add%20new%20columns%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1535791%22%20slang%3D%22en-US%22%3EYou%20can%20add%20the%20column%20in%20your%20new%20data%20source%2C%20when%20Power%20BI%20refreshes%20against%20the%20data%20set%20you%20will%20NOT%20see%20it%20in%20report%20designer.%20You%20will%20have%20to%20go%20into%20the%20Query%20editor%2C%20select%20the%20dataset%20%26amp%3B%20refresh%20the%20preview.%20It%20will%20then%20pick%20up%20the%20new%20column.%20It%20will%20now%20show%20in%20the%20report%20designer.%3CBR%20%2F%3E%3CBR%20%2F%3EHope%20that%20helps%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1535855%22%20slang%3D%22en-US%22%3ERe%3A%20Refresh%20query%20to%20add%20new%20columns%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1535855%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F734982%22%20target%3D%22_blank%22%3E%40Ed_K3%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EYou%20shall%20delete%20the%20step%20on%20you%20second%20screenshot%20(automatically%20generated%20Change%20Type)%20and%20use%20Change%20Type%20from%20the%20code%20instead.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1887251%22%20slang%3D%22en-US%22%3ERe%3A%20Refresh%20query%20to%20add%20new%20columns%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1887251%22%20slang%3D%22en-US%22%3EThank%20you%20so%20much%20for%20this!%20This%20was%20driving%20me%20crazy%20why%20my%20refresh%20wasn't%20adding%20the%20new%20columns%20to%20the%20data%20but%20was%20updating%20the%20rest%20of%20the%20data.%3CBR%20%2F%3EThank%20you%20again!%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1887278%22%20slang%3D%22en-US%22%3ERe%3A%20Refresh%20query%20to%20add%20new%20columns%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1887278%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F869030%22%20target%3D%22_blank%22%3E%40ColoCokeBoy%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EYou%20are%20welcome.%20Automatic%20Change%20Type%20is%20the%20core%20of%20many%20issues%2C%20I'd%20recommend%20to%20exclude%20it%20settings.%20At%20the%20same%20time%20I'd%20recommend%20to%20explicitly%20assign%20proper%20types%20at%20least%20before%20any%20merging%20and%20on%20final%20step.%20So%20far%20that's%20not%20critical%20in%20Power%20Query%20for%20Excel%20but%20could%20be%20an%20issue%20for%20other%20Power%20Query%20editions.%20That%20could%20require%20some%20extra%20efforts%20if%20number%20of%20columns%20is%20changed%20dynamically%2C%20not%20everything%20could%20be%20solved%20from%20user%20interface%20only.%20More%20time%20you%20invest%20in%20M-script%20better%20results%20you%20have.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
New Contributor

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

 

As you can see, each day new information is added to a new column.

My query is capturing new rows for new cases in different regions, but not the new columns for each new day's data. 

Can someone assist to advise how I can update the query to capture the new columns? 

 

Regards

Joel 

10 Replies
Highlighted

I have exactly the same problem. The best I can do is:

 

Refresh the query in Excel to flush the cache
Open a blank Excel

Open the Raw link in the blank Excel

Copy the query from Advanced Editor

Go to my original Excel, go to Advanced Editor and copy in the new query

 

Its a hassle and I am tempted to forget the query route altogether and just copy paste from the web site and use Text to Columns.

Highlighted
Best Response confirmed by Caesarus7 (New Contributor)
Solution

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

Highlighted

@Sergei Baklan thank you so much! this is exactly what i was looking for.

 

Regards

Joel 

Highlighted

@Caesarus7 

Joel, glad to help

Highlighted

@Sergei Baklan You have saved me a serious amount of time. Much appreciated.

 

@Caesarus7 If you swap the file reference to this:

 

Web.Contents("https://raw.githubusercontent.com/CSSEGISandData/COVID-19/master/csse_covid_19_data/csse_covid_19_time_series/time_series_19-covid-Confirmed.csv")

 

it will update online without having to download the csv.

Highlighted

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

Ed_K3_1-1595338885821.png
How do I fit everything in after that?

Ed_K3_2-1595339092640.png

 





 

 

Highlighted
You can add the column in your new data source, when Power BI refreshes against the data set you will NOT see it in report designer. You will have to go into the Query editor, select the dataset & refresh the preview. It will then pick up the new column. It will now show in the report designer.

Hope that helps
Highlighted

@Ed_K3 

You shall delete the step on you second screenshot (automatically generated Change Type) and use Change Type from the code instead.

Highlighted
Thank you so much for this! This was driving me crazy why my refresh wasn't adding the new columns to the data but was updating the rest of the data.
Thank you again!
Highlighted

@ColoCokeBoy 

You are welcome. Automatic Change Type is the core of many issues, I'd recommend to exclude it settings. At the same time I'd recommend to explicitly assign proper types at least before any merging and on final step. So far that's not critical in Power Query for Excel but could be an issue for other Power Query editions. That could require some extra efforts if number of columns is changed dynamically, not everything could be solved from user interface only. More time you invest in M-script better results you have.