Forum Discussion
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:
Any help will be great.
Thanks
2 Replies
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-query-and-power-bi/
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.
