Importing Multiple Web Pages with Different Column Names

[Anonymous]

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 butcher any of the explations, but hoping someone here may be able to help.

I'm attempting to import income statements, balance sheets, and statement of cash flows from Reuters to my Excel workbook. I first created a function to pull in the Key Metrics tables from the website for all of the stocks on my Excel Watchlist. I created a table with a formula to call out the URL for each specific stock, Imported Data From Table / Range, and created a Custom Column referencing the aforementioned function. Easy enough.

This is where I get lost. I attempted to replicate these steps to pull in the various financial statements for the same companies, but realized that column names wouldn't be identical for each URL due to varying fiscal years and reporting requirements across countries. I put two examples of this below, with ATVI having a standard fiscal year end at December 31st, and Apple having a different fiscal year end at September 30th. Because of these differences, I get an error code each time I create a Custom Column referencing the function I created.

Question is - is there a way to reference a column position within the Query Editor instead of a column name? I'd like to avoid pulling in the queries separately, as my Excel Watchlist has grown to 100+ possibilities since I started this hobby. Appreciate any feedback!

https://www.reuters.com/companies/ATVI.OQ/financials

https://www.reuters.com/companies/AAPL.OQ/financials

Best,

Mark

[jan_tothemoon]

Hi Mark, 

I've been trying to do something similar to what you´re doing as well but I haven't been sucessfull with M coding, think you could share me your file to grasp an idea to build my report oy maybe we can collaborate together. My goal is to build a complete stock analysis automated.

Regards, 
J.V.

[Anonymous]

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]

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

[Ehren · Accepted solution]

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}