Retrieve data by referencing a table within my Power BI File

Used for anything related to the new Power BI Desktop and Service tools
r.anand14@live.com
Posts: 14
Joined: Wed Nov 04, 2015 6:27 am

Retrieve data by referencing a table within my Power BI File

Postby r.anand14@live.com » Thu Jan 19, 2017 4:44 am

Hi team,

I have a working excel based power query which helps me retrieve list of all sharepoint url's, this is then saved as a table. This table thereafter is used as a reference to retrieve list of all contents from every sharepoint URL in the table. Now, I'm trying to use the same logic to retrieve data using Power BI. However, I do not know what is the expression to reference a table within PowerBI. Following is the manner in which I reference presently

Sites= Excel.CurrentWorkbook(){[Name="Projects"]}[Content]

Any ideas / suggestions will be helpful here.

Kind regards,
A!

MattAllington
Posts: 818
Joined: Sun May 04, 2014 4:01 pm
Location: Sydney, Australia

Re: Retrieve data by referencing a table within my Power BI File

Postby MattAllington » Fri Jan 20, 2017 4:39 pm

You want to extract a list of tables in a power bi data model already loaded?

Try writing a custom formula =#shared and filter from there
Matt Allington is Professional Self Service BI Consultant, Trainer and Author of the book "Learn to Write DAX". You can hire me at http://Exceleratorbi.com.au
http://exceleratorbi.com.au/what-is-power-pivot/
http://xbi.com.au/learndax

r.anand14@live.com
Posts: 14
Joined: Wed Nov 04, 2015 6:27 am

Re: Retrieve data by referencing a table within my Power BI File

Postby r.anand14@live.com » Fri Jan 20, 2017 9:09 pm

Hi Matt,

I think I should have detailed it better for an easier understanding, my apologies.

I have a working solution in excel with Power Query. What this does is
Step 1. extracts all the SharePoint url's for me in a list.
Step 2. load them as a list to a worksheet as step one.
Step 3. The next step it does is look up the loaded list of SharePoint URL's in the excel worksheet and get the contents from each of the SharePoint URL.

This works fine for in the excel application.

However, I would like to use Power BI to have this run as a working solution. Where am getting stuck is letting Power BI know is that it should use the list from the list of URL's populated i.e., (step 3) and get the content from each of the SharePoint. This is why my earlier question is there an equivalent to point to Power BI listed table instead of "Sites= Excel.CurrentWorkbook(){[Name="Projects"]}[Content]" or if there is another easier way to do this would be really grateful to know and use it.

Kind regards,
Anand

MattAllington
Posts: 818
Joined: Sun May 04, 2014 4:01 pm
Location: Sydney, Australia

Re: Retrieve data by referencing a table within my Power BI File

Postby MattAllington » Sun Jan 22, 2017 6:10 am

I am still not 100% clear and have some questions.

Why do you need to load the current solution into a worksheet in step 2? What does step 3 do that requires the data to be loaded to a worksheet? This is the key to answer you question. Unless Excel is doing something that can't be done in Power Query, I would not load the workbook to Excel in step 2. You can set it to Create Connection Only and then proceed from there within Power Query. The same should work in Power BI
Matt Allington is Professional Self Service BI Consultant, Trainer and Author of the book "Learn to Write DAX". You can hire me at http://Exceleratorbi.com.au
http://exceleratorbi.com.au/what-is-power-pivot/
http://xbi.com.au/learndax

r.anand14@live.com
Posts: 14
Joined: Wed Nov 04, 2015 6:27 am

Re: Retrieve data by referencing a table within my Power BI File

Postby r.anand14@live.com » Mon Jan 23, 2017 9:21 am

Hi Matt,

Thank you for the quick reply. Having said this, I do not know how to recall from a connection to proceed from step 1 to step 3 which is why I had dump the data in to excel sheet as step 2. Please guide me how to use the list from step 1 retain as connection and retrieve data. Many thanks in advance.

Kind regards,
Anand

Code: Select all

step one:

let
    Source = OData.Feed("https://ABC.sites.com/sites/DEF/siteData"),
in
    Source

Step two:

let
    Election.Results = (site) => let
        Source = sites.Tables(site),
        Lists = Source{[Name="Lists"]}[Content],
   #"Removed Other Columns" = Table.SelectColumns(Risks,{"Title", "Value"})

in
        Risks,
    Sites= Excel.CurrentWorkbook(){[Name="Lists"]}[Content],
    InsertedCustom = Table.AddColumn(Sites, "Custom", each Election.Results([site])),
in
  InsertedCustom

MattAllington
Posts: 818
Joined: Sun May 04, 2014 4:01 pm
Location: Sydney, Australia

Re: Retrieve data by referencing a table within my Power BI File

Postby MattAllington » Mon Jan 23, 2017 6:58 pm

It's not easy to find. After you have told a query to load to excel, you need to open the query pane, right click the query and select "load to". The set it to "only create connection". THe query will then be available in the query lane without being loaded in Excel. You can then right click on this query and select "reference" to start a new query from the last query
Matt Allington is Professional Self Service BI Consultant, Trainer and Author of the book "Learn to Write DAX". You can hire me at http://Exceleratorbi.com.au
http://exceleratorbi.com.au/what-is-power-pivot/
http://xbi.com.au/learndax

r.anand14@live.com
Posts: 14
Joined: Wed Nov 04, 2015 6:27 am

Re: Retrieve data by referencing a table within my Power BI File

Postby r.anand14@live.com » Tue Jan 31, 2017 2:16 am

Hi Matt,

I believe there is a confusion here, I'm trying to find a way to point to a table i.e., I have a query which uses Excel.CurrentWorkbook() to point at a table which work perfectly in excel however, if i would like to use the same logic in powerbi, i cannot use Excel.CurrentWorkbook() to point at a table within the workbook, is there an easier way? Following is the query am using to generate data. Any help here would be really helpful.

let
Path.Projects = (site) => let
Source = SharePoint.Tables(site),
Risks = Source{[Name="tbldata"]}[Content],
#"Removed Other Columns" = Table.SelectColumns(tbldata,{"Title", "CategoryValue"})

in
Risks,
Sites= Excel.CurrentWorkbook(){[Name="Lists"]}[Content],

Kind regards,
A!


Return to “Power BI Desktop/Service”

Who is online

Users browsing this forum: No registered users and 1 guest