Forum Discussion
Dynamically return all column names
Hi
In power query i have the following which dynamically reads the column names for outputting. Im just wondering if there is a cleaner solution whereby i dont have to transform each column but rather leave it as transform all columns in list. This will allow my source to have more columns than it does at present. Also can it determine the type of each column? Appreciate any help or advice on this as im new to Power BI
let
Source = Csv.Document(Web.Contents("http:xxxxx"),[Delimiter=",", Columns=18, Encoding=1252, QuoteStyle=QuoteStyle.None]),
#"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
DynamicNameHeader = Table.ColumnNames (#"Promoted Headers"),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{Table.ColumnNames (#"Promoted Headers"){0}, type text}, {Table.ColumnNames (#"Promoted Headers"){1}, type number}, {Table.ColumnNames (#"Promoted Headers"){2}, type number}, {Table.ColumnNames (#"Promoted Headers"){3}, type number}, {Table.ColumnNames (#"Promoted Headers"){4}, type number}, {Table.ColumnNames (#"Promoted Headers"){5}, type number}, {Table.ColumnNames (#"Promoted Headers"){6}, type number}, {Table.ColumnNames (#"Promoted Headers"){7}, type number}, {Table.ColumnNames (#"Promoted Headers"){8}, type number}, {Table.ColumnNames (#"Promoted Headers"){9}, type number}, {Table.ColumnNames (#"Promoted Headers"){10}, type number}, {Table.ColumnNames (#"Promoted Headers"){11}, type number}, {Table.ColumnNames (#"Promoted Headers"){12}, type number}, {Table.ColumnNames (#"Promoted Headers"){13}, type number}, {Table.ColumnNames (#"Promoted Headers"){14}, type number}, {Table.ColumnNames (#"Promoted Headers"){15}, type number}, {Table.ColumnNames (#"Promoted Headers"){16}, type number}, {Table.ColumnNames (#"Promoted Headers"){17}, type number}})
in
#"Changed Type"
- Anonymous6 years ago
I hadn't noticed. Try it this way (or a differente combination of text and number :-)):
#"Changed Type"=Table.TransformColumnTypes(Source, {DynamicNameHeader{0}, type text}& List.Transform(List.Skip(DynamicNameHeader,1), each {_, type number}))
- Anonymous6 years ago
Got it!....If i just remove Columns=18, it works then...thanks for your help
7 Replies
- AnonymousNot applicable
try to adapt this
#"Changed Type"=Table.TransformColumnTypes(youTable, List.Transform(DynamicNameHeader, each {_, type number}))
- AnonymousNot applicable
Thanks ....appears to be fine for all columns except the first one which is text and not a number....any solution. Do we need to specify the type or could it be determined by BI
- AnonymousNot applicable
I hadn't noticed. Try it this way (or a differente combination of text and number :-)):
#"Changed Type"=Table.TransformColumnTypes(Source, {DynamicNameHeader{0}, type text}& List.Transform(List.Skip(DynamicNameHeader,1), each {_, type number}))