Power Query Drops Leading Zero

[Anonymous]

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

[Anonymous]

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

[Anonymous]

@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

[Anonymous]

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]

@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

[Anonymous]

Sure, notice I changed the type to Text 

[Anonymous]

Dang... what are your settings?

Also which file is that? The problem to me happens in the dimension table.

Thank you,

Atanas

Atanas

[Anonymous]

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

[Jimmy801]

Hello @Anonymous 

can you share both of your csv-file? At least the part where the "error" occurs?

BR

Jimmy

[Anonymous]

@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 

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

Jimmy

[Anonymous]

Can you share your advance code for the dimension table please

[Anonymous]

@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

[Anonymous]

Odd, out of curiosity is the fact table CSV format? Also, in your original data source the "RAW Data Chargebacks BU Information 02-2021.csv" have you tried formatting the column in the actual CSV document to text?