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.
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.
- SergeiBaklanJul 19, 2023Diamond Contributor
But "Account Manager's Remarks" column appears from your source on the Promoted Headers step. Looks like above name simply doesn't exist in you source table.