Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Importing Multiple Web Pages with Different Column Names

Hi Everyone! I'm fairly new to the Power Query world, but have been creating my own stock valuation models during the pandemic and thought Power Query could help enhance that hobby. Sorry if I butche...
Need Help
  • Ehren's avatar
    Ehren
    4 years ago

    Hi Mark. You can get the columns positionally using the Table.ColumnNames function. For example:

    // Get the first column name
Table.ColumnNames(MyTable){0}

// Get the second column name
Table.ColumnNames(MyTable){1}
Anonymous's avatar
Anonymous
Not applicable
4 years ago

Thanks Ehran! Just returning to this project now - could you please help me with the code I have below? I'm importing data from WSJ, and would like it to update proactively as new financial statements are added. I'm hoping to change the column names "2020", "2019", etc. to reference columns 1, 2, etc. Thanks again!

 

let
Source = Excel.CurrentWorkbook(){[Name="Table3"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Name", type any}, {"Ticker", type text}, {"Exchange", type text}, {"Country Code", type text}, {"URLCFA", type text}, {"URLCFQ", type text}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "CFA", each fxCFA([URLCFA])),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Name", "Exchange", "Country Code", "URLCFA", "URLCFQ"}),
#"Expanded CFA" = Table.ExpandTableColumn(#"Removed Columns", "CFA", {"Fiscal year is January-December. All values CNY Millions.", "2020", "2019", "2018", "2017", "2016"}, {"Fiscal year is January-December. All values CNY Millions.", "2020", "2019", "2018", "2017", "2016"})
in
#"Expanded CFA"

Ehren's avatar
Ehren
Microsoft Employee
4 years ago

Instead of listing the column names, try something like this, which gets the column names from the first row of the CFA column:

 

Table.ExpandTableColumn(#"Removed Columns", "CFA", Table.ColumnNames(#"Removed Columns"{0}[CFA]))