Actual values showing as null when import an UNEDITED excel file.

[Anonymous]

Hi,

I have a normal excel file set that exports for another software. 

When i load it power query the total row values show as null. If i edit such as renaming the whole excel tab the actual values show as normal. 

This is the excel data -

Both in the load preview and query editor the total column values show all as null. 

When i change the excel tab name or edit it and save it the values show as normal. Similarly if i edit the cells and save they show as normal.

I can't see any options i can change in the source or query steps. How do i avoid this and have these values visible without editing the excel sheet? 

Thank you

[Anonymous]

I've found the cause issue. the excel file is in a protected view. If i enable editing in excel and save the numbers appear. 

I'm not sure there is a solution for this when importing data to power query, i imagine because of security and privacy issues.

[Jimmy801]

Hello @Anonymous 

but protected view is normaly a setting in the application, not in the file itself that you are accessing with a driver not with the application.

Jimmy

[Anonymous]

Yes interesting

This is the excel file

https://drive.google.com/file/d/1GST-VRamlXT0aoeJ53RkYWSsy3OeyanJ/view?usp=drivesdk

Even previewing it in Google drive the values in question aren't shown. However when downloaded and editing enable , it is

[Jimmy801 · Accepted solution]

Hello @Anonymous 

there is something wrong with your Excel-file. It has strangly only 6 kbyte, when an empty file already has 9. Open the file and save it solves the problem. Is this a file you created or is this some file that is created by another program as a download option? However, if you would have more of this erroneous files, you could write a makro that opens them and saves them.

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy

[Anonymous]

Thanks Jimmy for your help. 

 It is a file that I export from another software. 

It seems saving it is a step that needs to happen. 

There doesn't seem to be another export option. It is an issue that is consistent even in larger files [5000 rows +)

[PhilipTreacy]

Hi @Anonymous 

Is this an XLS file?  Sometimes loading data from XLS has some funny issues.

Can you try converting to to XLSX and then loading the file?

Phi

---

If I answered your question please mark my post as the solution.
If my answer helped solve your problem, give it a kudos by clicking on the Thumbs Up.

[Anonymous]

Hi Phil thanks for the reply,

Its a .xlsx file

this issue is consistent with other .xlsx files i've exported from "Cost-X"(software i've exported from). Its quiet annoying, i cant clean data from the file without editing something in it (and saving) so these values in the total column are shown in power query.

[PhilipTreacy]

Hi @Anonymous 

PQ will do an automatic type conversion right after loading the source data.  I'm guessing it's changed these Total columns to type text - note the abc in the column header.  The rate and subtotal columns are set to type any abc123 so these do appear as numbers.

Delete the Changed Type step towards the top of your query.  It's usually the 2nd or 3rd step.

Phil

---

If I answered your question please mark my post as the solution.
If my answer helped solve your problem, give it a kudos by clicking on the Thumbs Up.

[Jimmy801]

Hello @Anonymous 

surround your last step with Table.Buffer(YourContentOfYourLastStep)

Copy paste this code to the advanced editor in a new blank query to see how the solution works.

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy

[Anonymous]

No luck with that. I tried a Table buffer a couple ways doesn't appear to help. 

Even when i expand the table in the source step the values are shown as null. 

[Jimmy801]

Hello @Anonymous 

can you post the M-Code from the advanced editor here

BR

Jimmy

[Anonymous]

let
Source = Excel.Workbook(File.Contents("C:\Users\hh\Desktop\folder\Test power.xlsx"), null, true),
CostX_Sheet = Source{[Item="CostX",Kind="Sheet"]}[Data],
#"Promoted Headers" = Table.PromoteHeaders(CostX_Sheet, [PromoteAllScalars=true])
in
#"Promoted Headers"

[Jimmy801]

Hello @Anonymous 

I can't see any table buffer here. Can you try this

let
Source = Excel.Workbook(File.Contents("C:\Users\hh\Desktop\folder\Test power.xlsx"), null, true),
CostX_Sheet = Table.Buffer(Source{[Item="CostX",Kind="Sheet"]}[Data]),
#"Promoted Headers" = Table.PromoteHeaders(CostX_Sheet, [PromoteAllScalars=true])
in
#"Promoted Headers"

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy

[Anonymous]

I had a go. the issue still persist. Thanks for the follow up.

[Jimmy801]

Hello @Anonymous 

can you share the Excel-file?

BR

Jimmy

[Anonymous]

Thanks @PhilipTreacy , 

I did try that and it didn't work. 

it seems the null values are even present in the preview before loading it into the editor.