Unstructured data taking values from one record and combining those values with other records.

[Noxy]

Hi all,

Given records like these below

AAA,time,00:00:11
AAA,date,07-JUL-2023

CPU_ALL,T0001,56.1,23.5,0.4,20.1,79.6,32

CPU_ALL,T0002,57.1,22.2,0.3,20.4,79.3,32

AAA,time,00:10:11
AAA,date,07-JUL-2023

CPU_ALL,T0003,55.3,22.2,0.3,22.2,77.5,32

e.t.c

How can I create a date time value from the AAA records and add that into a new column on each subsequent CPU_ALL record until that date time changes again? The data comes from nmon on IBM AIX. I though it would be great if I could start creating visuals directly from the raw data using PowerBI.

Thanks,

Steve

[Noxy]

Hi whitch and thank you very much for sending me in the right direction.

I do have a further issue now in that given I am reading in any number of unstructured data files from a folder, my query steps even with 2 or 3 files of 10MB seem to take for ever and I see the bytes read going into the hundreds of MB.  I guess the data is being read multiple times as the query goes through the steps. Is there any way to make this more efficient? I am farily new to PowerBI. I was hoping that as I filter the data for the records I want to concentrate on that the query would get faster as it went through but that doesn't appear to happen.

[whitch]

Do you have the option to put steps 1 & 2 into a Dataflow?  This would force that stage to be evaluated only once.

[Noxy]

I'm not sure I have access to create a connection to a local folder from PowerBI online. The next button is greyed out. If I create a connection using a blank query and cut paste in from my original PowerBI desktop query, it comes up saying credentials are required to connect to the folder source. When I click to modify those, the connect button is greyed out. 

Going back to my original query, I was hoping that as I filter the data that the query would run faster as it completes through the query steps. However what I seem to see is multiple reads back to the original input text files. I have 3 10MB files but the read count bottom right is reaching hundreds of MB. I am new to PowerBI (probably mentioned that before). It would be good to read the data and filter it once. Does that form the data in the data model? Not sure of the correct terminology. Then the rest of the steps work on that cached data? 

[SRHayward]

Hi Noxy,

Did you manage to get this to work?  We have seen this before and developed a solution to import nmon data into PowerBI.  Please let me know if you would like to know more.

Stuart

[Noxy]

Hi Stuart, thanks for replying. Yes I'm all good now thank you. As I have progressed, I've actually ended up going back to the raw data and transforming / extracting what I need at that end which vastly speeds up the process. It was good to have a go at this to continue learning about PowerBI. Sadly I am addicted at the moment 🙂 Regards, Noxy

[SRHayward]

Hi Noxy,

Great to hear that all is good now.  For your interest, here is a link to an article I published on Linkedin regarding nmon data:

Long term nmon reporting 

At the time of publishing this, I was using another tool to import nmon data and produce charts.  Power BI brings this data to life and, as you say, becomes rather addictive 🙂

Stuart   

[whitch]

There are quite a few steps to doing this, but in overview:

1. Add a column which is 1 for "AAA,time,..." rows and 0 otherwise

2. Do a running total on this column.  Call this new column DateTimeID.   Advice on running totals: https://gorilla.bi/power-query/running-total/

          All rows which relate to the same DateTime (AAA-time, AAA-date, and CPU...) should now all have the same DateTimeID

3. Create 3 new tables in Power Query by referencing where we've got to so far.  Disable data-load and make no further changes to the table we're referencing.

3.1 Your first new table is your time table.  Filter only to AAA-time rows.  Exclude all columns except Time and DateTimeID.  Disable data-load.

3.2 Your second new table is your date table.  Filter only to AAA-date rows.  Exclude all columns except Date and DateTimeID.  Disable data-load.

3.3 Your third new table is your CPU table.  Filter only to CPU... rows.  Use DateTimeID to join in Date and Time from steps 3.1 and 3.2.

Let me know if you get stuck on any step & I will provide more detail.  I have assumed you are somewhat familiar with Power Query already.