cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
wpf_
Post Prodigy
Post Prodigy

how to convert a whole number to show 12 hour format

Looks like power query M is a complete different beast from DAX.  But I'm trying to create a new column in power query editor because i've been told it's generally better to add there then with DAX.  So I have a column of whole numbers that represent Hours of the day (0-23).  I'm trying to add a new column to convert that to 12 hour format ( 0 = 12:00AM, 13 = 1:00PM etc). Thanks.

1 ACCEPTED SOLUTION
Greg_Deckler
Super User IV
Super User IV

@wpf_ - Not sure who told you that, but it's perhaps true up until the point that it takes 10 times longer to do something than to just do it the way that you know how to do it. But, understood there are some Power Query bigots out there to be sure. But, after all, it ends up being a column in the data model regardless so it's not like it is the end of the world if you do in DAX or Power Query. Anway, you could do it like this in Power Query:

 

Time.FromText([Hour] & ":00:00")

 

Make sure your Hour column is text or you can convert it using Text.From 


---------------------------------------

@ me in replies or I'll lose your thread!!!

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

4 REPLIES 4
Greg_Deckler
Super User IV
Super User IV

@wpf_ - Not sure who told you that, but it's perhaps true up until the point that it takes 10 times longer to do something than to just do it the way that you know how to do it. But, understood there are some Power Query bigots out there to be sure. But, after all, it ends up being a column in the data model regardless so it's not like it is the end of the world if you do in DAX or Power Query. Anway, you could do it like this in Power Query:

 

Time.FromText([Hour] & ":00:00")

 

Make sure your Hour column is text or you can convert it using Text.From 


---------------------------------------

@ me in replies or I'll lose your thread!!!

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

@Greg_Deckler 

 

Awesome that worked!

Yea I was told by one of the guys on here that power query does a better job of compressing the data.  But it's good to hear that not everyone is on board with that.  I'm already having a hard time wrapping my head around dax, and now M.  : )

@wpf_ - Well, there is one guy in particular but I won't name names. Anyway, in that case, compression has nothing to do with Powre Query or DAX. Compression is in the Vertipaq engine. So, as you can see in the attached PBIX files, 1 was created all in Power Query and 2 was created all in DAX. 2 is actually a tiny amount smaller than 1 so whatever that guy was preaching is bogus IMHO. There are other reasons why you might want to use Power Query over DAX but compression has nothing to do with any of that.


---------------------------------------

@ me in replies or I'll lose your thread!!!

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




@Greg_Deckler ,

 

Thanks for that example.  Now I don't have to go crazy scrambling trying to learn M.  

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Get Ready for Power BI Dev Camp

Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.

Get Ready for Power BI Dev Camp

Power BI Dev Camp - September 30th, 2021

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

PowerPlatform 768x460.png

Microsoft Learn

Check out our new Discover Your Career Path blog post series and get all the details.

Top Solution Authors