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.
- Muhammad_FaheemJul 18, 2023Copper Contributor
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.
- SergeiBaklanJul 18, 2023Diamond Contributor
On which step that error appeared?
- Muhammad_FaheemJul 19, 2023Copper Contributor
SergeiBaklan Thank you Sir for responding.
Actually when i add a new column in my Excel Table and then refresh in Power Bi it appears but with null values and it removes my other Column (Account Manager's Remarks) in Power Bi. and i now i deleted the last step changed type the error is not showing now but i lost my last column.
- ColoCokeBoyNov 14, 2020Copper ContributorThank 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!- SergeiBaklanNov 14, 2020Diamond Contributor
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.
- siddhant1502Dec 07, 2021Copper Contributor
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?
- Ed_K3Jul 21, 2020Copper Contributor
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.
- Leon TribeMar 11, 2020MVP
SergeiBaklan 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.
- Caesarus7Mar 11, 2020Copper Contributor
- SergeiBaklanMar 11, 2020Diamond Contributor
Joel, glad to help