With a simple code as listed below, I tried to reproduce Excel formula "Hyperlink" in Power Query. Unfortunately, after loading the data to spreadsheet, Power Query will automatically add a single quote for each rows in the new added column. Is it a way to remove the single quote somewhere in the Power Query to make the hyperlink clickable? I would appreciate it.
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Added Custom" = Table.AddColumn(Source, "Hyperlink", each "=HYPERLINK("""&[Folder Path]&"" &[File Name]&""")")
in
#"Added Custom"
Hi Julian,
This isn't possible with Power Query for Excel. You can choose a Web URL data type in the Power BI model and in Power Pivot for Excel, as described here:
https://powerbi.microsoft.com/en-us/documentation/powerbi-service-hyperlinks-in-tables/
All I can suggest is you add a column to the Excel Table returned by Power Query that contains your HYPERLINK formula, as opposed to building it within Power Query.
Mynda
Hi Mynda,
I've been doing the way you suggested. What I could not figure out is that why Power Query "must" adds a single quote for an added column involving text type data operation.
Hi Julian,
My guess is so that the formula appears as text instead of a formula. If it entered =HYPERLINK(...) without the apostrophe it would be a formula, not text. The apostrophe forces the formula to become text.
Mynda
Hi Mynda,
It's the case for Excel formulas, but Power Query always add it automatically on a text type added column so far as I know. Nevertheless, it's still acceptable to "add a column to the Excel Table returned by Power Query that contains the HYPERLINK formula" as you said. Thanks a lot.
Julian
Hi Julian,
I tested Add Column to join a first and last name and set the data type as Text and it doesn't add an apostrophe. Power Query is adding the apostrophe because the text begins with an equals sign. Normally anything beginning with an equals sign is a formula in Power Query, so it adds the apostrophe so it knows to treat it as text.
Anyhow, the main thing is you have a workaround 🙂
Mynda
Hi Mynda,
I repeated the same experiment to confirm that Power Query would add an unexpected apostrophe on an added column. Please open the attached file then you know I was telling the truth.
Julian
Hi Julian,
Try this:
Remove the last formatting step (that formats the Full Name column as text)
Delete the results table, refresh the query, you will notice that PQ will not add the apostrophy, at least this is what happens on my side.
In excel, if you want excel to understand that what you type should be interpreted as text, even if you type numbers, you can do that by starting with an apostrophy. When you format a column as text, PQ is probably doing the same thing, will start that column text with that character. Anyway, it's visible only in formula bar, the char is not really in the cell.
Hi Julian,
I never said you were lying, just that in my test it didn't add an apostrophe. I couldn't reproduce your result from a new query, even using your example table in your last test.xlsx file. However, if I add a column to your query in the test.xlsx file it does indeed add the apostrophe.
I've attached your file again with my query and you'll see the M code is the same as yours yet I don't have an apostrophe.
If Catalin's suggestion doesn't work for you then all I can think is that you have a different version of Power Query, or there is a language setting issue that's responsible for the different results. What version of Excel and Power Query do you have and what language settings?
Mynda
Hi Mynda & Catalin,
Please accept my apoloy for not reply in good time due to my windows got a very bad problem unable to open it by all means since last Friday after last windows update. I've kept consulting Microsoft technical support guy in a hope to fix it. Unfortunately re-installation the operation system turned out to be the only option to me. That's really an unforgettable experience. Okay, back to the subject, I installed my Excel and Power Query using the following versions respectively:
Excel 2013 Stand alone ver. Office 64-bit
PowerQuery_2.49.4831.381 (64-bit) [en-us].msi
I also changed my regional setting to English (United States) in Power Query but the apostrophe still existed. I did another test by adding more than one records on the file julian_pq_text_join-1.xlsx and the apostrophe would not be added in both result tables However, the apostrophe added to the first original record still there. Perhaps that's the magic of M code.That's what I observed.
Hi Julian,
It's odd that adding records to the table resulted in subsequent rows not getting the apostrophe. It seems to be generating inconsistent results. It's a mystery to me, sorry.
Mynda
Hi Julian,
It may not be the ideal way to get around with it but it works. you can click the table created from power query. Then Table Tools => Design => Resize Table. Add Column A to the Table Created from power query. then use the hyperlink formula to make it works.
Thank you,
Danny
| Link | Name | File Path |
| 896522 - Copy.pdf | 896522 - Copy.pdf | C:UserszzzzzcOneDrive - zzzzzRequest896522 - Copy.pdf |
| 896522.pdf | 896522.pdf | C:UserszzzzzcOneDrive - zzzzzRequest896522.pdf |
| kkmmk - Copy.pdf | kkmmk - Copy.pdf | C:UserszzzzzcOneDrive - zzzzzRequestkkmmk - Copy.pdf |
| kkmmk.pdf | kkmmk.pdf | C:UserszzzzzcOneDrive - zzzzzRequestkkmmk.pdf |
| okl - Copy.pdf | okl - Copy.pdf | C:UserszzzzzcOneDrive - zzzzzRequestokl - Copy.pdf |
| okl.pdf | okl.pdf | C:UserszzzzzcOneDrive - zzzzzRequestokl.pdf |
| uikj - Copy.pdf | uikj - Copy.pdf | C:UserszzzzzcOneDrive - zzzzzRequestuikj - Copy.pdf |
| uikj.pdf | uikj.pdf | C:UserszzzzzcOneDrive - zzzzzRequestuikj.pdf |
| Add Column Table Tools => Design => Resize table |
From Power Query | |
| =HYPERLINK(C2,B2) |
Hi Danny,
Yes, I also used the way you did. Thanks.
Cheers,
Julian
