cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
nishi
Frequent Visitor

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

 

nishi_0-1669407348015.png

 

 

1 ACCEPTED SOLUTION
PhilipTreacy
Super User
Super User

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



Did I answer your question? Then please mark my post as the solution.
If I helped you, click on the Thumbs Up to give Kudos.


Blog :: YouTube Channel :: Connect on Linkedin


Proud to be a Super User!


View solution in original post

4 REPLIES 4
nishi
Frequent Visitor

@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.

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



Did I answer your question? Then please mark my post as the solution.
If I helped you, click on the Thumbs Up to give Kudos.


Blog :: YouTube Channel :: Connect on Linkedin


Proud to be a Super User!


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
Super User
Super User

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



Did I answer your question? Then please mark my post as the solution.
If I helped you, click on the Thumbs Up to give Kudos.


Blog :: YouTube Channel :: Connect on Linkedin


Proud to be a Super User!


Helpful resources

Announcements
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

BizApps LATAM 2023

Business Application LATAM Summit 2023

Join the biggest FREE Business Applications Event in LATAM this February.

Power Platform Bootcamp

Global Power Platform Bootcamp

In this bootcamp we will deep-dive into Microsoft’s Power Platform stack with hands-on sessions and labs, delivered to you by experts and community leaders.

Top Solution Authors
Top Kudoed Authors