date from yyyymmdd stored as whole number

[WMUBeene]

I have a large chunk of data that I have downloaded from our ERP system and am trying to work with.  The only hitch in this process is the that my date columns (AUDTDATE as the example below) are stored as yyyymmdd and PBI sees them as Decimal Numbers.  

The simple step of just changing the Data Type doesn't work (i tried).

Does anyone have any suggestions to convert these to usable dates?

[Anonymous · Accepted solution]

I'm not really sure why it's not working for you but you could try to create a new column with the following M-code:

=Date.FromText(Text.From([insert-your-column-name-here]))

I hope this helps,

Magnus

[haniewais]

I just wanted to add that the only way I got my date as int (yyymmdd) converted to text is using the Value.FromText first:

       Date.FromText(Text.From(Value.FromText([myDateAsInt]))))

Full line forrefrence:

      #"mySecondTable" = Table.AddColumn(

                   #myFirstTable, "DateAsDate", each Date.FromText(Text.From(Value.FromText([myDateAsInt])))

      )

[Anonymous]

I guess that could depend on the culture (Locale) settings you have for Power BI. On my machine for example I have Swedish and it works fine for me to change the data type to dates in the query editor when the values are stored in format yyyymmdd.

Otherwise you can create a new column in the query editor using the M-function #Date and extract year, month and day from the original date column to get a proper datevalue.

Br,

Magnus

[WMUBeene]

Thanks for the feedback.  I went into options/ reginal settings and switched my locale to Swedan.  This did not change the result.  It still Errors out when trying to convert.  Did you mean that I would need to adjust locale elsewhere?

Can you expand on how to use #date to make this happen.  I'm a newbie to M code and cannot seem to find any good information on how to apply this.

[Anonymous · Accepted solution]

I'm not really sure why it's not working for you but you could try to create a new column with the following M-code:

=Date.FromText(Text.From([insert-your-column-name-here]))

I hope this helps,

Magnus

[Kisame01]

Hi can this be made to display only the date? It is currently showing as 

10-Feb-19 00:00:00

Thanks allot

[WMUBeene]

I don't have a great dataset to test this one right now but there should be a "Data Type" option where you can swicth "Date/Time" to just "Date".  

[Anonymous]

Thanks!Solved my problem!

[Shiva799]

I'm starting with power bi for first time so i'm confused here Should it be like this Date.FromText(Text.From([BusinessDate])) or Date.FromText(Text.From(["BusinessDate"])) or Date.FromText(Text.From(['BusinessDate'])) Thanks In Advance

[WMUBeene]

Column name should be in quotes ("ColumnNameHere ")

[Shiva799]

I'm confused here should it be like Date.FromText(Text.From([BusinessDate]) or Date.FromText(Text.From(["BusinessDate"]) thanks in advance

[akhil5656]

This M-Code works perfectly for me. Thank you

[jadewind]

Thank you so much Magnus! It took me 2 hours to find the answer to this problem and your answer saved me!

[WMUBeene]

I fianlly got to try this solution over the weekend and it worked great.  Thank you for your insight.

[WMUBeene · Accepted solution]

Thank you Magnus, I will try this later for my education and let you know if it works for me later.  However, I found a really simple workaround, once I quite looking at the problem so hard, and want to share here for others.  It was a "I can't believe I didn't think of that sooner" moment.

The actual column containing the dates I needed is called DTCOMPLETE, below.

In the Query, I split this column into three columns call ReceiptYR, ReceiptMo, and RecieptDay.

In the table view, I created a calculated column using the Date function to bring all of this back together and format as needed.

ReceiptDate = DATE(PORCPL[ReceiptYR],PORCPL[ReceiptMo],PORCPL[ReceiptDay])