Data transform

[NB3]

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

[Greg_Deckler]

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.

[NB3]

@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

[Greg_Deckler]

What is your original data format?

[NB3]

@Greg_Deckler

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

Thanks,

[LaurentCouartou · Accepted solution]

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.