Hi there,
I am hitting a wall trying to figure out how to parse the information from a server log
| A | B | C | D | E | ||||||||||
| 03/19/2020 21:00:00 | 60 | 6 | Core | ankits@dantooine, martinn@kamino, mikam@outbreak, milesg@va1lvm0009, nerysl@luckless, timbo@zircon | ||||||||||
This is from a log from a license server that plots how many licenses per hour are being pulled.
Col A is the timestamp for usage, B is just the measure of minutes, C is the number of users, D is the Software and E is the user information I need to parse.
What I need to come up with is a column for the user (before the @) and a column for the machine (after the @) as users are not always on the same machine so we need to plot user and machine. So my report will be based around the software, with the date as the filter, the user will be mapped to a craft group and the machine will be represented somewhere else. I am just not sure for a single cell - E -, how to parse out the name before the @then the machine name after the @ but before the next , in order to then be able to use them as individual values for the date and product?
Bit stuck with this one.
Hi Steven,
Please see attached file for PQ solution.
If you find you have problems with this when used on real data, please attach a workbook with a sample table of that real data.
Regards
Phil
Hey Phil,
That looks good, but the only issue is the number of users in Col E wont always be 6 (that is my poorly created example). The number of users in Col C and then shown in Col E will change every single hour - higher or lower as it is gauging who has a license pulled as the hour clocks over.
Hi Steven,
It should be fine, I tested for that scenario when I wrote the query. Please see attached.
Regards
Phil
Thanks for the reply, not sure what happens with this.
I paste a line of data from the actual log in the spreadsheet and it can be refreshed, but it only adds 6 more lines. Plus when the Software changes the values were blank.
Hi Steven,
The easiest way is to split that column by delimiter, using comma as delimiter. Make sure you expand the Advanced Options and choose to Expand Into Rows, not into columns.
Then split again that column, but this time into columns and with @ delimiter.
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(Source, {{"User Info", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "User Info"),
#"Split Column by Delimiter1" = Table.SplitColumn(#"Split Column by Delimiter", "User Info", Splitter.SplitTextByDelimiter("@", QuoteStyle.Csv), {"User Info.1", "User Info.2"})
in
#"Split Column by Delimiter1"
Ended up with this.
let
Source = Excel.CurrentWorkbook(){[Name="Information"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Timestamp", type text}, {"Duration", Int64.Type}, {"Peak", Int64.Type}, {"Product", type text}, {"Users", type text}}),
#"Filtered Rows" = Table.SelectRows(#"Changed Type", each [Users] <> null and [Users] <> ""),
res = #table(List.RemoveLastN(Table.ColumnNames(#"Filtered Rows"))&{"User","Machine"},List.TransformMany(Table.ToRows(#"Filtered Rows"),each Text.Split(List.Last(_),","),(x,y)=>List.RemoveLastN(x)&Text.Split(y,"@")))
in
res
My only issue now is "Timestamp" at some point when the server creates the data loses its mind as there are two different data types in the one column!
There is always something 🙂
Hi Steven,
If you find you have problems with this when used on real data, please attach a workbook with a sample table of that real data.
If you can also supply some logs (at least a sample) that would be helpful.
Copying/pasting data into the sheet isn't the most reliable way to ensure data integrity. You'd be better to use PQ to read the logs.
Phil
Hi Steven,
This is cross posted to the PBI Community Forum
Please read https://www.myonlinetraininghub.com/excel-forum/forum-rules-and-guides/read-this-first
If you are going to cross post please tell us and link to it so you don't have multiple groups of people working on the same problem.
Phil
Hi Steven,
As I noted in the PBI forum, the date issue is due to them being in US format and you need to change them using Change Type -> Using Locale.
The attached query is the same as the one I posted on the Microsoft forum and loads data from the dummy CSV you supplied there.
Phil
