Extract underlying Excel cell's format

[junchoon]

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

[ImkeF]

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...

[junchoon]

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.