Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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"
Solved! Go to Solution.
Your Changed Type step should be:
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",
List.Transform(
List.RemoveFirstN(
Table.ColumnNames(Source),
13
),
each {_, Currency.Type}
))
Your Changed Type step should be:
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",
List.Transform(
List.RemoveFirstN(
Table.ColumnNames(Source),
13
),
each {_, Currency.Type}
))
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
... just found the answer by chance...:
= Table.TransformColumnTypes(>table<, List.Transform(>List-of-columnheads<, each {_, Number.Type}), "en-US")
Tested and this works thanks for the help 🙂
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.