Forum Discussion

nishi's avatar
nishi
Frequent Visitor
3 years ago
Solved

Text from a specific row based on codintion

Hi,

 

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

 

Table =

EnvironmentFileURL
HMLFileA.../sharepoint/HML/FileA.xlsx
PRDFileA.../sharepoint/PRD/FileA.xlsx
HMLFileB.../sharepoint/HML/FileB.xlsx
PRDFileB.../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

 

 

 

Need Help
  • PhilipTreacy's avatar
    PhilipTreacy
    3 years ago

    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

4 Replies

  • PhilipTreacy's avatar
    PhilipTreacy
    Super User
    3 years ago

    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

  • nishi's avatar
    nishi
    Frequent Visitor
    3 years ago

    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's avatar
      PhilipTreacy
      Super User
      3 years ago

      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's avatar
        nishi
        Frequent Visitor
        3 years ago

        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!