cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
NB3 Regular Visitor
Regular Visitor

Data transform

Hi everyone,

 

I can't seem to find a solution to adapt my table,

 

I want to create charts including the name AND the day for each Rep in the first column.

 

I guess I would just need to create another column with the names and fill it down but I'm not able to do that (FYI I am not allowed to change anything in the Excel spreadsheet)

 

Anyone has a solution to do that ?

 

And also, my talk time is supposed to be hours/min/scd (not AM) but Power BI doesn't allow me to have the figure without AM behind it.

 

Thanks a lot

 

Capture.PNG

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted

Re: Data transform

I  would try the following strategy:

 - create a conditional column based on Rep/Day and Dials

if [Dials] = null then [Rep/Day] else null

 - use the FillDown transform on the new column

 - filter the table and only keep the rows where [Dials] <> null

 

Formats in the editor window are not relevant, because you can set them in the data model. However, you may face issues if you have values totalling over 24 hours: the query editor knows about durations (it has a distinct data type for these data) but not the data model and formatting options for Time values do not include [hh]:mm:ss like in Excel.

5 REPLIES 5
Highlighted

Re: Data transform

I  would try the following strategy:

 - create a conditional column based on Rep/Day and Dials

if [Dials] = null then [Rep/Day] else null

 - use the FillDown transform on the new column

 - filter the table and only keep the rows where [Dials] <> null

 

Formats in the editor window are not relevant, because you can set them in the data model. However, you may face issues if you have values totalling over 24 hours: the query editor knows about durations (it has a distinct data type for these data) but not the data model and formatting options for Time values do not include [hh]:mm:ss like in Excel.

Super User
Super User

Re: Data transform

Wondering if @ImkeF has a solution to this in Power Query.

 

You should be able to change the Type of your Talk Time column to Duration to get rid of the AM.


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

Proud to be a Datanaut!


NB3 Regular Visitor
Regular Visitor

Re: Data transform

@Greg_Deckler

 

I tried to change to Duration but I get an error when I do that ... Any idea why ?

 

Thanks @LaurentCouartou it worked perfectly,

 

Thank you

Super User
Super User

Re: Data transform

What is your original data format?


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

Proud to be a Datanaut!


NB3 Regular Visitor
Regular Visitor

Re: Data transform

@Greg_Deckler

 

Unfortunately it's written as an hour of the day in the Excel spreadsheet ...

 

Thanks,