Feb 24 2020 06:02 PM
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
Mar 10 2020 12:13 PM
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.
Mar 10 2020 02:15 PM
SolutionTo 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.
Mar 10 2020 06:04 PM
Mar 11 2020 02:46 AM - edited Mar 11 2020 02:48 AM
@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.
Jul 21 2020 06:45 AM
@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.
How do I fit everything in after that?
Jul 21 2020 07:07 AM
Jul 21 2020 07:27 AM
You shall delete the step on you second screenshot (automatically generated Change Type) and use Change Type from the code instead.
Nov 14 2020 09:29 AM
Nov 14 2020 10:03 AM
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.
Dec 07 2021 11:17 AM
hi
I have connected sharepoint list with my power BI dashboard and now I have to add more columns to the source online sharepoint list. Power BI doesn't show the newly added columns in the query editor to be added to my existing dashboard. Do I have to build a dashboard from scratch with the updated sharepoint list or is there a way to work around without having to dump my existing dashboard?
Dec 07 2021 02:21 PM
You may play with M-code for your query. What exactly to check depends on which connector do you use, which version of it and which transformations are performed by query.
Mar 09 2022 01:12 PM
@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!
Mar 09 2022 03:36 PM
@patidallas22 Are you working in Power BI or Excel?
Mar 09 2022 05:28 PM
The short answer is go to the Data tab in Excel, the longer answer is described through the following link.
Mar 10 2022 07:24 AM
@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.
Mar 10 2022 07:38 AM
Jul 18 2023 05:51 AM - edited Jul 18 2023 05:52 AM
Hi Sir,
I want to add more column to my Excel workbook but i couldn't update it Power BI, it shows the below error, kindly feedback. thanks
When i added a new column, the column next to it goes missing as per below.
Jul 18 2023 12:15 PM
On which step that error appeared?
Mar 10 2020 02:15 PM
SolutionTo 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.