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

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
murthysydney
Frequent Visitor

Extracting Table for web -Between date Ranges - Error

Hi All,

I am extracting data from web URL in power bi,  when the URL is Web.Page(Web.Contents("https://cmmm.internalapps./Default.aspx?Group=3&Status=1&DateFrom=01/01/2000&DateTo=04/05/2022&IsAct...", [Timeout=#duration(0, 16, 40, 0)])) i get an error as below where i cannot see the ID column ending with 100 which has the data.

murthysydney_0-1651628076196.png

if i change the date 01/01/2019&DateTo=04/05/2022&IsActive=False

murthysydney_1-1651628240140.png

The Id column header ending with 100 has the table which is required. 

I am not sure which date range between dates has a table ending with 100. is there any solution to check-in power query.


 

Any suggestions will be of great help

 

Below is the M code:

let
Source = Web.Page(Web.Contents("https://cmweb.internalapps.?Group=" & Divisonlinkpara & "&Status=0&DateFrom=08/12/2000&DateTo=08/12/2022", [Timeout=#duration(0, 16, 40, 0)])),
#"Filtered Rows" = Table.SelectRows(Source, each ([Id] = "ChangeRequestListing_rgChangeRequests_ctl00")),
#"Removed Other Columns" = Table.SelectColumns(#"Filtered Rows",{"Data"}),
#"Expanded Data" = Table.ExpandTableColumn(#"Removed Other Columns", "Data", {"Column2", "Column3", "Column4", "Column5", "Column6", "Column7", "Column8", "Column9", "Column10", "Column11", "Column12", "Column13", "Column14"}, {"Column2", "Column3", "Column4", "Column5", "Column6", "Column7", "Column8", "Column9", "Column10", "Column11", "Column12", "Column13", "Column14"}),
// #"Changed Type" = Table.TransformColumnTypes(#"Expanded Data",{{"Column2", Int64.Type}, {"Column3", type text}, {"Column4", type date}, {"Column5", type any}, {"Column6", type text}, {"Column7", type text}, {"Column8", type text},
// {"Column9", type text}, {"Column10", type text}, {"Column11", type text}, {"Column12", Int64.Type}, {"Column13", Int64.Type}, {"Column14", Int64.Type}}),

OverrideZeroRowFilter = Table.View(
null,
[
GetType = () =>
type table[
Column2=Text.Type,
Column3= Text.Type,
Column4=Text.Type,
Column7=Text.Type,
Column8=Text.Type,
Column9 =Text.Type,
Column10=Text.Type,
Column11=Text.Type,
Column12=Text.Type,
Column13=Text.Type,
Column14=Text.Type,
Column5=Text.Type,
Column6 =Text.Type
],
GetRows = () =>
#"Expanded Data",
OnTake = (count as number) =>
if count = 0 then
#table(
type table[
Column2=Text.Type,
Column3= Text.Type,
Column4=Text.Type,
Column7=Text.Type,
Column8=Text.Type,
Column9 =Text.Type,
Column10=Text.Type,
Column11=Text.Type,
Column12=Text.Type,
Column13=Text.Type,
Column14=Text.Type,
Column5=Text.Type,
Column6 =Text.Type


],
{}
)
else
Table.FirstN(#"Expanded Data", count)]
),
#"Renamed Columns" = Table.RenameColumns(OverrideZeroRowFilter,{{"Column11", "Project Name"}, {"Column12", "Project Title"}}),
#"Added Custom" = Table.AddColumn(#"Renamed Columns", "Custom", each [Project Name]&[Column9]&" "&
[Column10]&[Project Title]),
#"Renamed Columns1" = Table.RenameColumns(#"Added Custom",{{"Custom", "Concatenate Project"}}),
#"Added Conditional Column" = Table.AddColumn(#"Renamed Columns1", "Gate 1", each if Text.Contains([Column13], "1") then 1 else null),
#"Added Conditional Column1" = Table.AddColumn(#"Added Conditional Column", "Gate 2", each if Text.Contains([Column13], "2") then 1 else null),
#"Added Conditional Column2" = Table.AddColumn(#"Added Conditional Column1", "Gate 3", each if Text.Contains([Column13], "3") then 1 else null),
#"Added Conditional Column3" = Table.AddColumn(#"Added Conditional Column2", "Gate 4", each if Text.Contains([Column13], "4") then 1 else null),
#"Added Conditional Column4" = Table.AddColumn(#"Added Conditional Column3", "Gate 5", each if Text.Contains([Column13], "5") then 1 else null),
#"Replaced Value" = Table.ReplaceValue(#"Added Conditional Column4"," ","",Replacer.ReplaceText,{"Column14"})
in
#"Replaced Value"

 

 

 

 

Thanks

 

6 REPLIES 6
v-kkf-msft
Community Support
Community Support

Hi @murthysydney ,

 

Maybe you can try adding queries to the Web.Contents, like this:

vkkfmsft_0-1651821149335.png


For more information, please refer to: Using The RelativePath And Query Options With Web.Contents() In Power Query And Power BI M Code 

 

Best Regards,
Winniz

 

Thanks, Winniz

The below is my URL:

https://cmweb.internalapps.hhhhhhhh.au/Default.aspx?Group=3Status=0&DateFrom=13/05/2000&DateTo=13/05...

So as you mentioned I have created the parameter to get the data but am not able to insert the full length of the URL.

 

 

let
Terms =
#table(
{"Term"},
{{"3"}, {"26"}, {"22"}}
),
SearchSuccessful = (Term) =>
let
Source =
Web.Page(Web.Contents(

"https://cmweb.internalapps.hhhhhhh/Default.aspx?Group=",
[Query=[q=Term]],
)
),
Success = Source[Data]
in
Success,
Output =
Table.AddColumn(
Terms,
"Search Successful",
each SearchSuccessful([Term])
),
#"Expanded Search Successful" = Table.ExpandListColumn(Output, "Search Successful"),
#"Expanded Search Successful1" = Table.ExpandTableColumn(#"Expanded Search Successful", "Search Successful", {"Column1", "Column2", "Column3"}, {"Search Successful.Column1", "Search Successful.Column2", "Search Successful.Column3"})
in
#"Expanded Search Successful1"

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

How do we add this additional end text of the URL("Status=0&DateFrom=13/05/2000&DateTo=13/05/2022&IsActive=False") in the Source set.

Please assist.

 

Thanks

 

 

Hi Mate,

 

I tried using your suggestion did not work.



let
Terms =
#table(
{"Term"},
{{"3"}, {"26"}, {"22"},{"18"},{"25"},{"21"}}
),
SearchSuccessful = (Term) =>

 

 

 

 

let
Source = Web.Page(Web.Contents("https://cmweb.internalappsmmmm/Default.aspx?Group=3&Status=1&DateFrom=01/01/2010&DateTo=09/05/2023&I...")),
Data5 = Source{5}[Data],
#"First Row as Header" = Table.PromoteHeaders(Source),


#"Changed Type" = Table.TransformColumnTypes(Data5,{{"", type text}, {"Column2", Int64.Type}, {"Column3", type text}, {"Column4", type date}, {"Column5", type text}, {"Column6", type text}, {"Column7", type text}, {"Column8", type text}, {"Column9", type text}, {"Column10", type text}, {"Column11", type text}, {"Column12", type text}, {"Column13", type text}, {"Column14", type text}, {"Column15", type text}, {"Column16", type text}, {"Column17", type text}}),

 

AlternativeOutput=#table(type table [Column2= text,Column3=text,Column4=text,Column5=text,Column6=text,Column7=text,Column8=text,Column9=text,Column10=text,Column11=text,Column12=text,Column13=text,Column14=text,Column15=text,Column16=text,Column17=text],
{{"Error", "Error", 0}}),
TestForError= try Source,

 

Output = if TestForError[HasError] then AlternativeOutput else #"Changed Type"
in
Output,
Output =
Table.AddColumn(
Terms,
"Search Successful",
each SearchSuccessful([Term])
),
#"Expanded Search Successful" = Table.ExpandTableColumn(Output, "Search Successful", {"Column2", "Column3", "Column4", "Column5", "Column6", "Column7", "Column8", "Column9", "Column10", "Column11", "Column12", "Column13", "Column14"}, {"Column2", "Column3", "Column4", "Column5", "Column6", "Column7", "Column8", "Column9", "Column10", "Column11", "Column12", "Column13", "Column14"})
in
#"Expanded Search Successful"

murthysydney
Frequent Visitor

Thanks, Ibendlin

Is it possible to use if statement in the source:

 

if the below URL finds an ID that contains 100 then get data from the table if not move to the next date, can it be checked date in the loop function?

If You are able to assist with an example will be great.

 Let
Source = Web.Page(Web.Contents("https://cmweb.internalapps.?Group=& Divisonlinkpara & "&Status=0&DateFrom=08/12/2000&DateTo=08/12/2022", [Timeout=#duration(0, 16, 40, 0)])),

I don't have access to your web server.  I cannot help any further.

lbendlin
Super User
Super User

I am not sure which date range between dates has a table ending with 100. is there any solution to check-in power query.

 

In short, no.  Power Query has no idea about the underlying logic of your data source.  It is your responsibility as a developer to implement code that adheres to the logic.

Alternatively you could extend the scope of the request (for example by not specifying a date range) and then do the filterning further downstream in Power Query.

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

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

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.