Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors