cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
akhil5656
Frequent Visitor

How to change decimal data type to date format in power BI

I am new to Power BI, in my data base the date column is in the format:20160301, when I was trying to insert this date column in the power BI  table it is taking as integer and giving the total.In below Images clearly shown.

MY Question is how to change 20160301 format to 03/01/2016 format in the Power BI.

Can any one explain to me how to change the format?

Thanks

1.png2.png

1 ACCEPTED SOLUTION
kaushikd
Resolver II
Resolver II

@akhil5656

Here is a work around

I have created a new column and used the Date function.

 

I have taken the orderdate from adventure work

Capture.PNG

 

Now Follow the steps:-

Step 1. R.Click on the table<FactInternetSales> and select New Column.

Step 2. Paste the code--->Date = Date(LEFT(FactInternetSales[OrderDateKey],4),Right(LEFT(FactInternetSales[OrderDateKey],6),2),Right(FactInternetSales[OrderDateKey],2))

 

Here i have done some workaround as Date function intakes 3 parameter Date(<Year>,<Month>,<Day>), so i have extracted it using LEFT() and Right() Functions.

 

Now you can see the new column you created is in date type.

 

 

Please dont forget to mark this as a solution if this is what you wanted.

View solution in original post

2 REPLIES 2
kaushikd
Resolver II
Resolver II

@akhil5656

Here is a work around

I have created a new column and used the Date function.

 

I have taken the orderdate from adventure work

Capture.PNG

 

Now Follow the steps:-

Step 1. R.Click on the table<FactInternetSales> and select New Column.

Step 2. Paste the code--->Date = Date(LEFT(FactInternetSales[OrderDateKey],4),Right(LEFT(FactInternetSales[OrderDateKey],6),2),Right(FactInternetSales[OrderDateKey],2))

 

Here i have done some workaround as Date function intakes 3 parameter Date(<Year>,<Month>,<Day>), so i have extracted it using LEFT() and Right() Functions.

 

Now you can see the new column you created is in date type.

 

 

Please dont forget to mark this as a solution if this is what you wanted.

View solution in original post

MarcelBeug
Community Champion
Community Champion

Alternatively, in Power Query, you can import the data as text and then change it to date (in a separate step), e.g.

 

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type text}}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Changed Type",{{"Date", type date}})
in
    #"Changed Type1"
Specializing in Power Query Formula Language (M)

Helpful resources

Announcements
Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

User Group Leader Meeting January 768x460.png

Calling all User Group Leaders!

Don't miss the User Group Leader meetings on January, 24th & 25th, 2022.

Jan 2022 Dev Camp 768x460 copy.png

Power BI Dev Camp- January 27th, 2022

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors
Top Kudoed Authors