Change Table formatting

[Yo421]

Hello,

Can someone help me please?
I've the "Original-table" file (it's an extract from my ticketing software) and i'd like to generate the "Wishes-table" automatically (...in order to use it with Power BI)
How can i do it?

to

 

[p45cal]

See attached, table at cell F2. Works on the premise that column A Incident Numbers are numbers (therefore can't contain letters or other characters).

 

[Yo421]

Thanks for your help.
For the the example, the ticket number is only a series of numbers, but in reality, the ticketing tool generates a ticket number of the form INC-12345 or DEM-12345 (Inc for incident and Dem for asked).
How can i do this, with such ticket number?

 

[p45cal]

Will there ALWAYS be a hyphen in the ticket number?

 

[Yo421]

YES always

 

[p45cal]

Then as long as only the ticket numbers have hyphens in that column and nothing else (the dates for example) the attached tweak should work. It assumes that anything with a hyphen in that column is a ticket number.

 

[Yo421]

Excuse me but i don't see how can i do to convert the file.
How should i use this file to convert it?

Thanks

 

[p45cal]

it's an extract from my ticketing software

Can we see (read: Can you attach) that extracted file BEFORE you try and open/view it in Excel? It must never have been touched by Excel! If the ticketing software can produce a CSV or a TXT file or something else, that may be better than output in Excel format. It will be easier to point Excel (Power Query) to the file rather than load the data into Excel yourself - it also means when you update the file, Excel will be update itself without you having to do anything more than refreshing the result table.

What ticketing software is it?

The solution I gave you converts the Table in the sheet. It's a Power Query query. So if you change the data in the Table you can right-click the result table and choose Refresh to update that table (like you would a pivot table).

 

[Yo421]

The ticketing software is BMC.
For time tracking (ticket management), the only format is Excel (no csv or txt). The BMC tool sends me an Excel file every week and the goal would be with Power BI to use it to visually generate statistics.
Concretely, with the file you have created, what actions should I take to convert the file that I receive every week?

Thank you in advance

 

[p45cal]

I would never manually open the file you receive each week.
In a separate workbook, a Power Query query pointing at the received file each week; the pointed-at file could be a file which is in a constant location and name, being replaced each week with a new file, or we could point the query to the new file each week by having the new file name and location in a cell in the same workbook as the query (we could add a little vba (macro) code to browse for that file, or we could have you typing it in manually).
Say how you want it to work.

In any event, I need to see the exported xlsx file without it having been opened and saved (prefereably without it ever having been opened).
Then I'll write what's needed.

 

[Yo421]

Ok
Can i send you in private the file?

 

[p45cal]

See if you can attach it in a Private Conversation here.

 

[Yo421]

I sent you the file privately.
For the way to do it, keep it simple with an identical file name.
Anyway, the action of copying the generated table to save it in a new formatted file will be carried out manually (the set of files formatted every week for Power BI will be stored in a folder)

 

[Yo421]

.... a subsidiary question concerning the "Xh and Ymin" column: does an excel formula exist to convert this column into minutes (and not into text)?

Thank you in advance

 

[p45cal]

concerning the "Xh and Ymin" column: does an excel formula exist to convert this column into minutes

This too can be converted in Power Query but I think it might be better converting the date/time stamps into real Excel date/time stamps within Power Query, then you'll be able to subtract the start date from the end date and get a duration from that (still all within Power Query). One question, are ALL the date/time stamps always CEST (because I don't know yet how to keep/convert such time zone data, so would propose throwing away the time zone part)?

I've attached a file in the Private Conversation but this hasn't addressed this last question - it'll have to wait.

 

[Yo421]

Thank you so much.
Yes the timestamp is always CEST and this can be deleted without problem.