Forum Discussion
Change column types dynamically
Hi Everyone,
I have an issue with changing column types in the Power Query Editor. Normally i change them in the UI but this time my column names can change and there can be more or less columns in the data then currently is the case. So i wish to change all columns (Except the first 13 columns (They are always the same)) to the Currency type (Fixed Decimal Number). After some time on google I created the code below that doesn't have any syntax errors in the advanced editor however it gives me another error after pressing Done in the advanced editor.
ERROR:
Expression.Error: We cannot convert a value of type Table to type List.
Details:
Value=[Table]
Type=[Type]
This is the code i use in the advanced editor that gives above error
let
Source = Csv.Document(Web.Contents("https://*******"),[Delimiter=",", Columns=179, Encoding=65001, QuoteStyle=QuoteStyle.None]),
#"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{Source,
List.Transform(
List.RemoveFirstN(
Table.ColumnNames(Source),
13
),
each {_, Currency.Type}
)})
in
#"Changed Type"
Your Changed Type step should be:
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers", List.Transform( List.RemoveFirstN( Table.ColumnNames(Source), 13 ), each {_, Currency.Type} ))
4 Replies
- JakintaSolution Sage
Your Changed Type step should be:
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers", List.Transform( List.RemoveFirstN( Table.ColumnNames(Source), 13 ), each {_, Currency.Type} ))- Gerald23Helper I
Tested and this works thanks for the help 🙂
- RaiStaNew Member
Hi, great solution!
Only, I'd like to know, how to use this 'List.Transform'-function with regional code (for instance "en-US" in case the system is not in US-american setting) - how to get this into the List.Transform-function?Thanks in advance,
RaiSta
- RaiStaNew Member
... just found the answer by chance...:
= Table.TransformColumnTypes(>table<, List.Transform(>List-of-columnheads<, each {_, Number.Type}), "en-US")