Text from a specific row based on codintion

[nishi]

Hi,

I have a static table where I will manage Sharepoint URL's for multiple files, i.e:

Table =

Environment	File	URL
HML	FileA	.../sharepoint/HML/FileA.xlsx
PRD	FileA	.../sharepoint/PRD/FileA.xlsx
HML	FileB	.../sharepoint/HML/FileB.xlsx
PRD	FileB	.../sharepoint/PRD/FileB.xlsx

I have a parameter where I am going to choose the Environment, HML or PRD, based on this selection, my tables will have their sources refreshed dynamically. 

Right now, I managed to get the following:

Table.SelectColumns(Table.SelectRows(Table, each [Environment] = Parameter and [File] = "FileA"), "URL")

Let's say the 'Parameter' is set to PRD, then this is returning:

URL

.../sharepoint/PRD/FileA.xlsx

I know this must always return a single result otherwise it won't work.

The problem is that this is returning a table, and I need it to return a text so I can add the text as the link to sharepoint.

let
Source = Excel.Workbook(Web.Contents(Table.SelectColumns(Table.SelectRows(Table, each [Environment] = Parameter and [File] = "FileA"), "URL")), null, true)
in
Source

[nishi]

@PhilipTreacy Perfect! this worked perfectly!

Is there a M function that could do the same transformation or it is one of those things you won't find any official reference? 

Just asking because I read and tried loads of functions before coming here.

[PhilipTreacy]

Hi @nishi 

There's no function to do this.  You just have to know that table columns are lists.  You can access/store the column by referring to the table name and the column like so:  Table[Column]

Lists are like arrays and are indexed from 0, so to access the first item in the list you can use Table[Column]{0}

I'm sure I've seen a Microsoft article talking about lists, records and tables but can't locate it now, sorry.

Regards

Phil

[nishi]

Great @PhilipTreacy 

To be honest, the concept of lists, arrays, tuples and so on are straight forward to me as I have some base of Python, my question was just to confirm that sometimes I have to abstract the functions and deep dive into basic programming.

I am learning M now thinking on functions fisrtly but it seems they are not always mandatory.

Thanks for the help!

[PhilipTreacy · Accepted solution]

Hi @nishi 

This will return text rather than a table

= Table.SelectColumns(Table.SelectRows(Table, each [Environment] = Parameter and [File] = "FileA"), "URL")[URL]{0}

The way this works is that I've added [URL]{0} to the end of the line.

[URL] extracts the column called URL from the table.  This column is a list.

{0} extracts the first item in that list, the URL that you want.

regards

Phil