Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Reply
junchoon
New Member

Extract underlying Excel cell's format

I have difficulties finding Excel cell's underlying format via Power Query

 

For example, several formatted cells which shows the amount and the Currency associated with each amount, where the formatting is per cell and each cell may or may not have the same currency format as the others:

 

In Execl, you can see

USD 75

USD 76

EUR 80

EUR 81

RMB 88

etc.

 

But in Power Query, you will see

75

76

80

81

88

 

In VBA, this is pretty simple - invoke something like cell.NumberFormat, you will be able to extract result like these:

USD

USD

EUR

EUR

RMB

 

But Power Query, I could not find a solution

 

Similar topic was found in another Microsoft site about two years back, but with no solution:

 

https://social.technet.microsoft.com/Forums/en-US/57169c12-4089-46ec-b881-8b6aa9e610c4/how-to-get-an...

 

Any help will be great.

 

Thanks

2 REPLIES 2
ImkeF
Super User
Super User

There is no native function in Power Query to achieve this, so you'd have to go down the rabbit hole and extract the xlsx to its pieces and then try to reassemble. You'll find a start to this procedure and some additional links in this blogpost: https://www.thebiccountant.com/2017/08/18/how-to-import-from-excel-with-cell-coordinates-in-power-qu...

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

Hi 

 

Thanks for the reply.  

 

I looked at the link you provided.  As suggested, I looked at the replies, and notice one of them has sample Power Query code.  I am still new to Power BI / Query, thus I may not understand fully the syntax.

 

Is there a Power Query function call that we can execute something like Excel.Sheet.Cell.NumberFormat?  Or simply Cell.NumberFormat? 

 

Thanks.

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Top Solution Authors
Top Kudoed Authors