Forum Discussion
Power Query Drops Leading Zero
Jimmy801 and Anonymous ,
Thank you both for your suggestions.
There are few things i noticed since yestdreday.
1. Both files (RAW Data) are identical and formatted like each other.
2. For some reason in the Fact table data set power query adds the leading zero.
3. For some reason in the Dimension table data set power query drops out the leading zero.
4. When i upload both data sets in the query editor my default type for all columns is "Text". Have you noticed that on your end? When i uploaded an excel file, the default type for all columns is "123 ABC". That's something i've never noticed so far.
5. When i copied the RAW data from the csv files, pasted, and saved it in .xlsx the power query editor drops the leading zeros in both files. Which is a workaround... but originally i need them.
6. When i copied the RAW data from the csv files, pasted, and saved it in .xlsx and formatted it like official excel table, and uploaded them the power query editor drops the leading zeros in both files. Which is a workaround... but originally i need them.
7. When i formatted the columns in the RAW data for both data sets in CSV and Excel, the result was the same as points 6 and 7.
Any ideas will be deeply appreciated,
Atanas
Hello Anonymous
can you share both of your csv-file? At least the part where the "error" occurs?
BR
Jimmy
this is a really interesting issue. Maybe you could remove anything that might breach GDPR or anything which is sensitive and share a copy of the raw data with us? Just upload them to onedrive and create a sharable link.
The last thing I'm curious about is, are you using Power Query building into Power BI or Power Query directly in Excel??Hey Atanas,
The two CSV files you have shared have already dropped the leading 0's for me.I think the column defaulted back to "general", rather than "text" when you sent over the file.
It's odd when I change the column type to "Text" on the CSV. Update 20874 to 020874 (on the CVS), It comes into Power BI correctly on both tables.Anonymous- thank you!
Can you please show me screenshot from the query editor?
I am thinking something might be different in my settings... when I upload the file i use this:
It is by default. But i tried that one as well:
None of them seem to work properly... both in excel and power bi. Can you please show me your settings?
Really appreciate your help,
Atanas
Sure, notice I changed the type to Text
