Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet 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
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
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
Thanks, Winniz
The below is my URL:
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"
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.
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.
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
88 | |
86 | |
84 | |
68 | |
49 |
User | Count |
---|---|
138 | |
111 | |
103 | |
64 | |
60 |