Extracting Table for web -Between date Ranges - Error

[murthysydney]

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.

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

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

[v-kkf-msft]

Hi @murthysydney ,

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

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

[murthysydney]

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

[murthysydney]

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]

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)])),

[lbendlin]

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

[lbendlin]

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.