Expression error

[Anonymous]

Hi

Need some quick help pl as new here. Am getting similar error. 

Learning to replicate as in https://www.youtube.com/watch?v=STjBoS1rQuQ&list=LL&index=11&t=311s 

Pl do redirect to a tutorial if any

Reached half way to create fx in pbi:

= (URL) as table =>
let
Source = Web.Page(Web.Contents([URL]))
in
#"fxMovies"

and 

let
Source = Excel.Workbook(File.Contents("C:\Users\ssh130\OneDrive\pbi\linked.xlsx"), null, true),
Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
#"Changed Type" = Table.TransformColumnTypes(Sheet1_Sheet,{{"Column1", type text}}),
#"Renamed Column

[PhilipTreacy]

Hi @Anonymous 

In addition to changing your function code as I said

(URL as text) =>
let
    Source = Web.Page(Web.Contents(URL))
in
    Source 

You need to change the code that calls the function.  It should be this

fxMovies([URL])

You need () around the [URL]

Regards

Phil

[Anonymous]

Thanks Phil

Appreciate the learning support!

Do we have a course to refer to else have to keep asking here.

I have 1 hopefully last ques as query is not executing. Am also not sure if there is another way to input a list except upload an excel file which somehow is failing to connect through One drive.

---------------------

let

    Source = Excel.Workbook(File.Contents("C:\Users\ssh130\OneDrive\pbi\linked.xlsx"), null, true),

    Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],

    #"Changed Type" = Table.TransformColumnTypes(Sheet1_Sheet,{{"Column1", type text}}),

    #"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"Column1", "URL"}}),

    #"Added Custom" = Table.AddColumn(#"Renamed Columns", "fetch", each fxMovies([URL])),

    #"Expanded fetch" = Table.ExpandTableColumn(#"Added Custom", "fetch", {"Data"}, {"fetch.Data"}),

    #"Expanded fetch.Data" = Table.ExpandTableColumn(#"Expanded fetch", "fetch.Data", {"Company Name.....

Best Rgds

Saurabh

[PhilipTreacy]

Hi @Anonymous 

How exactly is it not executing?  Is it not loading the list of URL's from the Excel workbook?

There are other ways to enter lists.  If the list isn't very long.

In PBI Desktop you can try clicking on Enter data

Which gives ytou this window where you can type in the list of URL's, name the column and name the table

Clicking on OK will then create a table in Power Query.

This can be loaded into your main query in the Source step like this, followed by the other steps.  Note you don't need the Changed Type and Renamed Columns steps in this scenario.

let
    Source = URLTable,

    #"Added Custom" = Table.AddColumn(#"Renamed Columns", "fetch", each fxMovies([URL])),

    #"Expanded fetch" = Table.ExpandTableColumn(#"Added Custom", "fetch", {"Data"}, {"fetch.Data"}),

    #"Expanded fetch.Data" = Table.ExpandTableColumn(#"Expanded fetch", "fetch.Data", {"Company Name.....

In terms of Power Query courses there are a several you can choose between, here's a couple you could check out

https://www.myonlinetraininghub.com/excel-power-query-course

Power Query Academy - Skillwave Training

Regards

Phil

[Anonymous]

Thanks. Yes it does not load from excel, tried csv as well. Is there are a way to keep the list dynamic and link to another file?

[PhilipTreacy]

Hi @Anonymous 

Loading from an Excel file or a CSV should work.  What exactly is the error message you are getting?

Can you upload (either here or to OneDrive, Google Drive or DropBox) your Excel workbook or CSV to test?

Regards

Phil

[Anonymous]

Thanks. It worked as txt (notepad) so sorted 🙂

Only issue I see is I get partial data. Is there a way to bypass the 'more' button to fetch all data?

[PhilipTreacy · Accepted solution]

Hi @Anonymous 

Unfortunately Power Query doesn't have the ability to interact with web pages by doing things like clicking buttons.

But, if you click MORE it loads this page

https://www.moneycontrol.com/stocks/marketstats/bse-gainer/all-companies_-1/more/

So you could include that in your list of URL's and it will fetch that page's data.

Regards

Phil

[PhilipTreacy]

Hi @Anonymous 

Change your function code to this

(URL as text) =>
let
    Source = Web.Page(Web.Contents(URL))
in
    Source 

Regards

Phil