Dynamically return all column names

[Anonymous]

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"

[Anonymous · Accepted solution]

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}))

[Anonymous]

try to adapt this

#"Changed Type"=Table.TransformColumnTypes(youTable, List.Transform(DynamicNameHeader, each {_, type number}))

[Anonymous]

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

[Anonymous · Accepted solution]

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}))

[Anonymous]

Great thanks your suggestion worked.....one further question....any way to have the number of columns dynamic. at present im expecting 18 but this could change depending on the source. Works fine if my source has less but not sure what would happen if source data had more than 18 columns

let
Source = Csv.Document(Web.Contents("xxx"),[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", {DynamicNameHeader{0}, type text}& List.Transform(List.Skip(DynamicNameHeader,1), each {_, type number}))
in
#"Changed Type"

[Anonymous]

try it:

it should work for any number of columns provided that the first is numerical and the other textual.

perphs.

[Anonymous]

No it dosnt work....i changed it to expect 8 but 18 was sent in source and only 8 displayed. Only works if the source has actually less than the expected number...in this case the expected columns are just blank

[Anonymous · Accepted solution]

Got it!....If i just remove Columns=18, it works then...thanks for your help