SOLVED

Power Query/PowerBI Extract some values from complex CSV

Iron Contributor

Dear Experts,

                  From attached file, I need to create a Report like below:-

Each of the Report entry has following parameters:-

So from "TB Top " we need to extract "Frame" & "SubFN" ; from "TB Config" we need "CW Index"

and from the "CB" we need "CRC"

anupambit1797_1-1701467887251.png

 

 

anupambit1797_0-1701467876965.png

Thanks in Advance,

Br,

Anupam

 

12 Replies

Hi @anupambit1797 

 

(Not a specifically a 'Power BI Topic')

 

I looked at your case. IMHO, as it is, it's above the scope of a forum/community (my view only). Getting the expected result (not cleary defined yet) requires:

 

#1 Analysis of the stucture of records of say 3-5 IDs (between 2 '1980 Jan 12 wxyz LTE LL1 PDSCH Decoding Results')
#2 For IDs where > 1 CRC values exist, do you report the 1st, the last, the min, the max..., all?
#3 Manually or via formula do the job on say 3 different & representative IDs to allow checking the query output
#4 Build the corresponding query (sounds doable but not trivial)

 

If you provide #1-3 (accurate) in an Excel workbook, I can take a look at #4

Hope this helps & makes sense

Hi@L z. ,

Comments inline

 

#1 Analysis of the structure of records of say 3-5 IDs (between 2 '1980 Jan 12 wxyz LTE LL1 PDSCH Decoding Results')

[Anupam] Answered in 2
#2 For IDs where > 1 CRC values exist, do you report the 1st, the last, the min, the max..., all?

[Anupam] CRC supported values are only "0" or "1", and need to report all the CRCs( 0,1) for each of the records.

Algo should be like => Each Record has a Unique value of "Frame , SubFN",

So , From each record:-

We need :-

Frame, SubFN => from TB Top

CW Index => from TB Config

and CRC => from CB


#3 Manually or via formula do the job on say 3 different & representative IDs to allow checking the query output

[Anupam] Attaching a sheet(Sheet_3) doing manually for 3 records below:-

1980 Jan 12 04:29:31.203 [76] 0xB132 LTE LL1 PDSCH Decoding Results

1980 Jan 12 04:29:31.286 [BB] 0xB132 LTE LL1 PDSCH Decoding Results

1980 Jan 12 04:29:31.527 [1A] 0xB132 LTE LL1 PDSCH Decoding Results

 

Thanks in Advance,

Br,

Anupam

Hi @anupambit1797 

 

Could you revise the title of this thread to help people who Search please?

i.e. 'Power Query/PowerBI Extract some values from complex CSV'

 

Re. #1 and #2 I was more looking at something like:

Sample.pngto help me work on #4. Done in attached file based on 3 different UIDs

 

To ensure we're on the same page, while I work on #4, could you please provide the expected result for '1980 Jan 12 04:29:42.758 [D8] 0xB132 LTE LL1 PDSCH Decoding Results' (table prepared in attached file, sheet 'Expected Result'?

Hi@L z. , I updated the Title as per your recommendation , and attached the expected excel.

 

Br,

Anupam

Hi @anupambit1797 

 

Attached is '1980 Jan 12 04:29:42.758 [D8] 0xB132 LTE LL1 PDSCH Decoding Results' from the CSV you initially shared

 

In the Expected_Result you posted (I don't undersand why you did not use my file...) - just the first 2:

Sample.png

Could you tell me where you find Frame = 828 and Frame = 832 in the attachment ???

Hi@L z. , the one I attached is from the whole dump file, to give an example on how the expected result should look like,

 

Br,

Anupam

Hi @anupambit1797 

 

OK but that's not what I asked & need

I asked - to make sure what I'm working on meets your expectations - that you provide me the expected result for '1980 Jan 12 04:29:42.758 [D8] 0xB132 LTE LL1 PDSCH Decoding Results' in the workbook I earlier shared

 

If it's too much asking forget and we'll leave it there, no problem

Hi@L z. , I tried to do the same manually for the Record you mentioned , for ~6 Records, Expected result in Attached "Anupam_Expectation"sheet. Used a Unique Identifier "CB Index" CB-> # and TB Records

anupambit1797_0-1702056435674.png

 

Thanks in Advance,

Br,

Anupam

 

Hi @anupambit1797 

 

It seems we're on the same page

In attached file my values are obtained by formulas & query

best response confirmed by anupambit1797 (Iron Contributor)
Solution

Hi @anupambit1797 

I just realized I forgot to push you the query the other day :(

Hi@L z. 

              Greetings!

              I tried to use this calculator again, but seems not working, attached is the raw txt file.

 

While changing the source, it's giving an err:-

anupambit1797_0-1707885442026.png

Can you please help on what wrong I am doing?

 

Thanks in Advance,

Br,

Anupam

Hi @anupambit1797 

 

I tried to use this calculator again, but seems not working, attached is the raw txt file

As I earlier mentioned your initial call for help required significant analysis, before putting in place a non-trivial query that was based on the file you shared at that time. So, IF the new text file isn't formatted exactly the same, new analysis + query update (or totally new one) are probably required

 

While changing the source, it's giving an err:-
Your screenshot doesn't show the error in Power Query but it seems obvious you have to update the file path in Excel as it still points to my PC folders' structure

 

That's all I can do for now (didn't download your file) as my PC died a few days ago and the new one hasn't landed yet

1 best response

Accepted Solutions
best response confirmed by anupambit1797 (Iron Contributor)
Solution

Hi @anupambit1797 

I just realized I forgot to push you the query the other day :(

View solution in original post