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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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
Fabcon_Europe_Social_Bogo

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

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

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.