Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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
Proud to be a Super User!
@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
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!
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
Proud to be a Super User!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
101 | |
49 | |
19 | |
12 | |
11 |