Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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
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.
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
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
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
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 +)
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.
Proud to be a Super User!
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.
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.
Proud to be a Super User!
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
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.
Hello @Anonymous
can you post the M-Code from the advanced editor here
BR
Jimmy
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"
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
I had a go. the issue still persist. Thanks for the follow up.
Hello @Anonymous
can you share the Excel-file?
BR
Jimmy
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
100 | |
54 | |
21 | |
12 | |
11 |