How to tabulate a table with blocks in two formats in Power Query?

César Castañeda 1 Reputation point
2022-12-14T08:27:13.673+00:00

Hi all,

May someone could help me with this. I have a table that I've got after import a text file with not homogeneous format.

The data appears in blocks (in yellow block type 1 and 2) that begins with parameter A to G (here for ease the parameters are a letter of one length but actually are words). Within each block [A to G] are sub blocks (in blue). So when tabulate the values of those sub blocks should go in different lines.

For the lower part of the data with second format (a mix of first format and new format), the blocks of data begins with parameter "B" until "R". Then comes some consecutive lines arranged in 4 columns. The four columns have 4 headers that are DX, HQ, MB, GZ.

This is the table I have so far in M code

let  
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jVNdb4JAEPwvPFty3KHUR1C4Sg+0x5lqqSF+tGlqH5q0mv787hkal41FEsLNLjczu5NQlk6kpqP7yixnceU5Pad+Vr3SCQF4qI4A9FE9AiBQXcyj6qTWlBkDYKiOSd1C8wkNTyPpdK174DntHpzscUucB8R5iOqE6rU6B1ecOXEeEueOUXmE5nejBS1uCZHt6C5JnCge3mReiiMLJ/mNELidARgZreMCNTWAsQozPMgC0N2DFbHK8unUTTOr9XxgTGzzuVIn9AKtRZrDe7M5uod3xrj7uf8I2MD9Or7u1t9rxA2VmsJRGIPui//vX/CK8rOX7zHLFXBgbntM9L+T01koSUaRnvQNiciEanw1Ir37gSLZ7mx/Uw/qcbFvDKjjFD7Bijq1Eywf64uC87egWwzS/xP3hmfG6hc=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [COL1 = _t, COL2 = _t, COL3 = _t, COL4 = _t])  
in  
    Source  

This is the image of the table:

270511-image.png

and this is the desired output I'm trying to get:

270512-image.png

Thanks in advance for any help.

Community Center | Not monitored
0 comments No comments
{count} votes

Your answer

Answers can be marked as 'Accepted' by the question author and 'Recommended' by moderators, which helps users know the answer solved the author's problem.