Date column values not populating inspite of correct formats in source file and power bi

[adbm]

Hi,

I've read all sorts of solutions to my problem but im still stumped. I have no idea why PBI won't show me my date values in the date column which is formatted identically in excel and power bi. I have a sales register of a company with multiple columns like invoice number, amount, customer name, po date, shipping date, etc. I have loaded this file into Power bi. Although my shipping date column has values of dates in the excel source file, this column in appearing blank in power query. There excel file has rows which are blank and rows which have date data. There are around 880,000 rows so its a large data. But i have run many other analysis on this data with no problems. I have checked and rechecked the source file for problems like formulas, spaces etc and not found any. The date column is also formatted the same in both power bi and excel. I cant imagine why this wouldn't work. and PBI seems to throw the strangest of errors every time i refresh my data to try and get it to work. The latest error i have got is "Expression.Error: We cannot convert the value #date(2024, 1, 4) to type Text.
Details:
Value=04-01-2024
Type=[Type]"

This error is absurd as the column is not type 'Text' at all. It is type 'Date'.

I hope someone can tell me what im doing wrong here and how i can resolve it. it seems like such a small hiccup but its taking so hours to close it.

Thank you!

[Anonymous]

Hi @adbm ,

Thanks audreygerred for the quick reply. I have some other thoughts to add:

(1) This is my test data.

(2) When I change the column to date type directly in the power query, I get the error. We can split the column, then merge the columns in order and finally change the type of the column.

Best Regards,

Neeko Tang

If this post  helps, then please consider Accept it as the solution  to help the other members find it more quickly. 

[audreygerred]

Hi! Can you please upload a copy of your file? The number of rows is pretty small for Power BI. If you cannot upload the pbix, can you paste of copy of the M-code from Advanced Editor in Power Query?

[adbm]

hi,

here's a copy of the m-code. (Not allowed to share the file due to confientiality agreement with client.)

let
Source = Excel.Workbook(File.Contents("D:\OneDrive - Natali\OneDrive\Natali 2024-25\Data Analytics 24-25\FP\Sales\Client data\Sales Register.xlsx"), null, true),
#"SALES REGISTER_Sheet" = Source{[Item="SALES REGISTER",Kind="Sheet"]}[Data],
#"Promoted Headers" = Table.PromoteHeaders(#"SALES REGISTER_Sheet", [PromoteAllScalars=true]),
#"Added Custom" = Table.AddColumn(#"Promoted Headers", "Discount as a % of sales", each [Discount]/[#" Gross Value"]),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Discount as a % of sales"}),
#"Changed Type" = Table.TransformColumnTypes(#"Removed Columns",{{"Shipping Date ", type date}, {"Invoice Date ", type date}, {"Invoice Quantity", type number}, {" Gross Value", type number}, {" Net Value", type number}, {"CGST ", type number}, {"SGST", type number}, {"IGST ", type number}, {"PO Date ", type date}, {"Delivered Quantity ", type number}}),
#"Added Custom1" = Table.AddColumn(#"Changed Type", "GST Code", each Text.Start([#"GSTIN "],2)),
#"Added Custom2" = Table.AddColumn(#"Added Custom1", "Unit Price", each [#" Net Value"]/[Invoice Quantity]),
#"Added Index" = Table.AddIndexColumn(#"Added Custom2", "Index", 0, 1, Int64.Type),
#"Replaced Value" = Table.ReplaceValue(#"Added Index","#N/A","",Replacer.ReplaceText,{"Shipping Date "}),
#"Changed Type1" = Table.TransformColumnTypes(#"Replaced Value",{{"Unit Price", type number}}),
#"Added Custom3" = Table.AddColumn(#"Changed Type1", "Unit Price on Gross", each ([#" Gross Value"]/[Invoice Quantity])),
#"Changed Type2" = Table.TransformColumnTypes(#"Added Custom3",{{"Unit Price on Gross", type number}, {"GST Code", Int64.Type}, {"Shipping Date ", type date}, {"Discount", type number}})
in
#"Changed Type2"