Forum Discussion

littlemojopuppy's avatar
littlemojopuppy
Community Champion
1 year ago
Solved

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

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 th...
Need Help
  • shafiz_p's avatar
    shafiz_p
    1 year ago

    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

ZhangKun's avatar
ZhangKun
Super User
1 year ago

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