Power Query treating embedded URLs from Excel file strangely...

[littlemojopuppy]

Hi!  And welcome to my issue...would appreciate any help you might be able to provide.  🙂  This is very strange and I've been unable to figure this out.

Importing data from an Excel file, and that file has a field or two with URLs to Power BI reports that could be one of the following:

• Null/blank values (pink in snip)
• Other garbage values (orange)
• A full URL with the format of "https//..." (underlined in red)
• A text value with the URL applied as formatting (yellow)

The problem is with the URLs applied as formatting.

The intention is to determine which values are a complete URL in the form of "https://..." and keep them, otherwise change the value to null.  This is the relevant M code where I test if the text value starts with "https://" 

    #"Replaced Non-Links" = Table.ReplaceValue(#"Reordered Columns", each [ Helpful Report Links], each 
        if Text.StartsWith([#" Helpful Report Links"], "https://") = true then [#" Helpful Report Links"] else 
        null, 
        Replacer.ReplaceText,{" Helpful Report Links"}),
    #"Added Custom" = Table.AddColumn(#"Replaced Non-Links", "Checking", each Text.StartsWith([#" Helpful Report Links"], "https://"), Logical.Type),
    #"Reordered Columns1" = Table.ReorderColumns(#"Added Custom",{"Report Number", "Project Track ", "PBI Team", "Report Developer Name", "Scope", "Business Process/ Specific Area", "Business Owner (SME)", "Report Type", "Report Name/ Use Case", "Report Description/ Intent", " Helpful Report Links", "Checking", " General Priority", "Report T-Shirt Size", "Build & Testing Notes ", "Report Progress Status", "Report Health Status", "Teams Responsible for Dependencies", "Team Responsible for Block or Delay", "DnA Deliverable Name(s)", "DnA Delivery Date", "DnA Handoff Date ", "Target Start ", "Target Completion", "Actual Start", "Actual Completion", "Final Reporting Deliverable(s)", "Technical Documentation", "Training Material"})
in
    #"Reordered Columns1"

The last two steps are for testing my logic and showing the results next to where the transformation is applied.  Here's the results

The test of Text.StartsWith() is correctly determining whether text value starts with "https://" but it is not converting the highlighted values to null. I've seen posts where Power Query cannot see any formatting, etc. from the Excel file and can only work with the values shown.  Not understanding why this isn't working...

Thanks for reading this 🙂

[ZhangKun]

First of all, we should reach a consensus that power query can only read the values ​​in the Excel file, and cannot obtain the links in the values.

Next, let's discuss why the result of the formula is wrong.

You used the Replacer.ReplaceText function, which cannot accept null as a parameter. If used, it will only report an error or do nothing. A more detailed explanation is:

• Text.StartsWith returns true, replacing the value of column B (equivalent to doing nothing).
• Text.StartsWith returns false or null, replacing null, but null is not text, so it will not be replaced.

There are many solutions. The simplest one is to replace Replacer.ReplaceText with Replacer.ReplaceValue function. Another method is to use the following solution:

let
    Source = Table.FromColumns(List.Repeat({{null, "", "abc", "xabc"}}, 2), {"A", "B"}),
    Replace = Table.ReplaceValue( 
        Source, null, null,
        (txt, old, new) => if Text.StartsWith(txt, "ab") = true then txt else null,
        {"B"}
    )
in
    Replace

[shafiz_p · Accepted solution]

Hi @littlemojopuppy  Try use table.transformColumns :

ReplacedNonLinks = Table.TransformColumns(#"Changed Type", {{"Helpful Report Links", each if Text.StartsWith(_, "https://") then _ else null, type nullable text}})


Where #"Changed Type" is the previous steps.

Output:

Hope this helps!!

If this solved your problem, please accept it as a solution and a kudos!!

Best Regards,
Shahariar Hafiz

[littlemojopuppy]

Hi @shafiz_p had to make one small change to check if value was null before TextStarts() but otherwise worked perfectly.  Thank you!  🙂

[suparnababu8]

Hi @littlemojopuppy 

Can you pls provide a sampl excelsheet/data with out any sensitive information. Thanks

[lbendlin]

please provide a sample Excel file and indicate the expected outcome.