#1
|
|||
|
|||
Power Query Formatted Text
Hi all,
Excuse my ignorance but until today, I was not aware that Excel had a query function. I have an Excel file and an Access file that I can use interchangeably with a Word Add-In. The add-in pulls data from a selected record in the Excel or Access file and puts the record field result in a document content control. One short coming with the process is that when I extract the data from either and Access table or Excel file using ADODB where the column in the record source is formatted as Date, Percentage, Currency etc. the result in the Word form is the not the "literal" value displayed in the data source e.g., Saturday, 18 November 2023 is displayed as 11/18/2023, 10% is displayed as .10, $45.30 is displayed as 45.3 I can work around this issue in Access by creating a query that returns a text value formatted as either a long date, a percentage, or currency amount. When the data is extracted from the query using ADODB, the "literal" value is retrieved. I'm attaching and image of the query datasheet view and SQL view. I understand that it is possible to create a query in Excel as well. I have managed to create a query, but I can't figure out how (if it is even possible) to do something similar with the Excel. Where my Excel query returns the date, percentage, currency values of its parent worksheet as text string values formatted as long date, percentage and currency. Any help much appreciated. |
#2
|
|||
|
|||
Greg, are your destination cells formatted correctly?
|
#3
|
|||
|
|||
Apparently not. When I create the query, the values are converted to "General" formatting. I can apply "Date", "Percentage" and "Currency" but that puts me back in the same position before I started. I need the values to be "text" formatted as long dates, percentages or currency.
Will attach a sample file if you think you can help. Thanks |
#4
|
||||
|
||||
What I did:
1.click on From Data Range to open Power Query and set source 2. click on Add Column > Custom Column - enter alias field name in place of "Custom" then enter expression: Number.ToText([Cost],"C") Review Number.ToText - Function | Power Query How How do you cast an integer to a string in Excel's PowerQuery - Stack Overflow Last edited by June7; 11-19-2023 at 11:45 AM. |
#5
|
|||
|
|||
This issue is resolved see: [SOLVED] ADODB Get actual displayed values
The solution is a workaround using an Excel Power Query. |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Excel power query help | ganesang | Excel Programming | 0 | 09-20-2021 01:50 AM |
Calculate age in power query as of a given date | Marcia | Excel | 1 | 11-12-2020 07:14 AM |
Format numbers in Power Query | Marcia | Excel | 2 | 09-21-2020 12:59 AM |
Do I need a power query for this one? | shabbaranks | Excel | 5 | 04-10-2019 03:29 AM |
Query and Power Query not working Excel 2016 | bl10 | Excel | 0 | 07-22-2016 06:25 PM |