Forum Discussion
Power Query Drops Leading Zero
Dear community,
Hope my message finds you well!
I have 2 data sets which contain 1 fact table and 1 dimension table.
Both data sets are CSV files and all columns are formatted with type "General"
Here is example from the fact table:
And here is an example from the dimension table:
However, when i upload them in the query editor for some reason the fact table shows me a leading zero (which according to the original data source is correct):
But for some reason it drops it out in the dimension table:
Thus I have blank in my pivot.
Can you please help me understand why that might be. Both columns are formatted as text and both tables have the same data transformations... But for some reason i miss the leading 0 in the dimension.
Any help will be appreciated.
All due respect,
Atanas
13 Replies
Anonymous
Sure, please see it below:
let
Source = Csv.Document(File.Contents("C:\Users\Rhinswind\Desktop\RAW Data Chargebacks BU Information 02-2021.csv"),[Delimiter=",", Columns=24, Encoding=65001, QuoteStyle=QuoteStyle.None]),
#"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
#"Removed Other Columns" = Table.SelectColumns(#"Promoted Headers",{"Customer Name", "Sales Invoice Number", "BU", "Market Segment", "Vertical Market", "Product Family"}),
#"Inserted Merged Column" = Table.AddColumn(#"Removed Other Columns", "Merged", each Text.Combine({[Sales Invoice Number], [Customer Name]}, "-"), type text),
#"Renamed Columns" = Table.RenameColumns(#"Inserted Merged Column",{{"Merged", "Transaction And Name"}}),
#"Removed Duplicates" = Table.Distinct(#"Renamed Columns", {"Transaction And Name"}),
#"Trimmed Text" = Table.TransformColumns(#"Removed Duplicates",{{"Sales Invoice Number", Text.Trim, type text}}),
#"Cleaned Text" = Table.TransformColumns(#"Trimmed Text",{{"Sales Invoice Number", Text.Clean, type text}})
in
#"Cleaned Text"I tried even to clean and trim it, as i thought it might work.
Please let me know if anything else is needed.
Thank you,
Atanas
Jimmy801Community Champion
Hello Anonymous
do you have any chance to change the xlsx-file? If yes I would reccomend you to save them as a table in Excel and then read from this table instead of your sheet.
Hope this helps, otherwise let me know
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
JimmyJimmy801 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
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- Jimmy801
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
Hello guys,
Anonymous and Jimmy801
Sure, here's link to the files. I uploaded both XLSX and CSV version of the files.
https://drive.google.com/drive/folders/1g8SXAaKHlYiJhITkfZ0e6lu3prv9gd-D?usp=sharing
Once you request access i will give it immediately, if I am not sleeping, which won't happen for the next 10-11 hours.
In the Fact table you will find region, transaction number and amount.
In the dimension you will find Transaction number and BU (business unit).
The relationship between both tables is based on the transaction number column.
The number of the transaction with the missing leading zero is originally 020874, but in the query editor is 20874.
I tried both Power BI and Excel, the results were the same.
All due respect,
Atanas