Forum Discussion

cgkas's avatar
cgkas
Helper V
3 years ago
Solved

Tabulate a table with blocks in different formats

Hi all,

 

May someone could help me with this, I'm stuck in this. I asked in social.technet but it seems there is no PQ/PBI support there anymore.

 

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 how it looks the input table

 

 

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

 

 

Thanks in advance for any help.

Need Help
  • wdx223_Daniel's avatar
    wdx223_Daniel
    3 years ago
    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]),
    Custom1 = let fx=each not List.IsEmpty(List.RemoveItems(_,{"",null})) in Table.SelectRows(Table.Combine(Table.Group(Source,"COL2",{"n",each let a=Table.Skip(_),b=Table.FirstN(a,each [COL3]="" or [COL3]=null),c=Table.Skip(a,Table.RowCount(b)) in Table.PromoteHeaders(Table.FromColumns(List.Select(Table.ToColumns(Table.Transpose(b))&Table.ToColumns(c),fx)))},0,(x,y)=>Byte.From(y="" or y=null))[n]),each fx(Record.ToList(_)))
 in
     Custom1
  • wdx223_Daniel's avatar
    wdx223_Daniel
    3 years ago

    i'm trying to split the code to more steps

    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]),
    fx=each not List.IsEmpty(List.RemoveItems(_,{"",null})),
    group0=Table.Group(Source,"COL2",{"n",each _},0,(x,y)=>Byte.From(y="" or y=null)),
    group1=Table.TransformColumns(group0,{"n",each let a=Table.Skip(_),b=Table.FirstN(a,each [COL3]="" or [COL3]=null),c=Table.Skip(a,Table.RowCount(b)) in [a=a,b=b,c=c]}),
    group2=Table.TransformColumns(group1,{"n",each Table.ToColumns(Table.Transpose([b]))&Table.ToColumns([c])}),
    group3=Table.TransformColumns(group2,{"n",each List.Select(_,fx)}),
    group4=Table.TransformColumns(group3,{"n",each Table.FromColumns(_)}),
    group5=Table.TransformColumns(group4,{"n",each Table.PromoteHeaders(_)}),
    combine=Table.Combine(group5[n]),
    Custom1 = Table.SelectRows(combine,each fx(Record.ToList(_)))
 in
     Custom1

4 Replies

  • wdx223_Daniel's avatar
    wdx223_Daniel
    Community Champion
    3 years ago
    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]),
    Custom1 = let fx=each not List.IsEmpty(List.RemoveItems(_,{"",null})) in Table.SelectRows(Table.Combine(Table.Group(Source,"COL2",{"n",each let a=Table.Skip(_),b=Table.FirstN(a,each [COL3]="" or [COL3]=null),c=Table.Skip(a,Table.RowCount(b)) in Table.PromoteHeaders(Table.FromColumns(List.Select(Table.ToColumns(Table.Transpose(b))&Table.ToColumns(c),fx)))},0,(x,y)=>Byte.From(y="" or y=null))[n]),each fx(Record.ToList(_)))
 in
     Custom1
    • cgkas's avatar
      cgkas
      Helper V
      3 years ago

      Thanks so much for your help. I'm very new to M code and I don´t understand the logic and how it works your solution. Is there a way like debug in VBA or like "Evaluate Formula" in MS Excel to see the output of each nested function from inside to outside? Or a tip in how to evaluate step by step? Thanks again

      • wdx223_Daniel's avatar
        wdx223_Daniel
        Community Champion
        3 years ago

        i'm trying to split the code to more steps

        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]),
    fx=each not List.IsEmpty(List.RemoveItems(_,{"",null})),
    group0=Table.Group(Source,"COL2",{"n",each _},0,(x,y)=>Byte.From(y="" or y=null)),
    group1=Table.TransformColumns(group0,{"n",each let a=Table.Skip(_),b=Table.FirstN(a,each [COL3]="" or [COL3]=null),c=Table.Skip(a,Table.RowCount(b)) in [a=a,b=b,c=c]}),
    group2=Table.TransformColumns(group1,{"n",each Table.ToColumns(Table.Transpose([b]))&Table.ToColumns([c])}),
    group3=Table.TransformColumns(group2,{"n",each List.Select(_,fx)}),
    group4=Table.TransformColumns(group3,{"n",each Table.FromColumns(_)}),
    group5=Table.TransformColumns(group4,{"n",each Table.PromoteHeaders(_)}),
    combine=Table.Combine(group5[n]),
    Custom1 = Table.SelectRows(combine,each fx(Record.ToList(_)))
 in
     Custom1