Forum Discussion

brunofds's avatar
brunofds
Helper I
5 years ago
Solved

Moving values from columns

Hello there   I'm struggling with this silly issue in PQ. I have multiples pdfs tables as a data source.  Due to the nature of these pdfs tables, after some work in PQ, the result I got is someho...
Need Help
  • Jimmy801's avatar
    Jimmy801
    5 years ago

    Hello brunofds 

     

    add a new column where you are generating your new table. Then delete all other columns and expand the new columns

    here the formula

    [FirstColumn = if Text.StartsWith([A],"Taxa de Administração do Agente Financeiro cobrada em") then [A] else [B],
SecondColumns = if Text.StartsWith([A],"Taxa de Administração do Agente Financeiro cobrada em") then [B] else [C]]

    here the complete code

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("tVJNT8JAFPwrLz1BrNBSMHBsAE/4kcKNcHi0D7JJu9vsLsSfo/FA8O4v6B9ztyBRATWgt9d0dt68mRmPnRE+ICQEYZIxzpSWWKyKZwGJgHBOXBNcM448JiYFxGIqMUGgDLyg7jXqDa/hOa4TtH3X88FMfJGmzsT9K95aJ2i6QecTczm4TgUuoNqVxTphWthNXUvCixVatLcHvcc5ZmZxic1RxpSigkvzjBtxWiijIoOItJBcHKYIcyE1KRhSLEmjZEdW9Wgp0sX23iPLbu3PiCibilQVT0tiao9sGA56d9C/gcCve35pisX47WYz+IgblGeXxykoHmHnykF1EfHEiLHZ5CmLbS6v5qhdHFuh35r5WyfsLrZxPaIclUKTf06pgJHIzCz/18DTgvfbV63WiVX7GkWvWE8PJXEk2pO4znH6zDL83LyKeV81XxGpOeoyAATjO1czksULj011UEAv7PdB0qwGJi4talaR7dVsIy5+V7DhnbwB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [A = _t, B = _t, C = _t]),
    #"Added Custom" = Table.AddColumn(Source, "CreateNewTable", each [FirstColumn = if Text.StartsWith([A],"Taxa de Administração do Agente Financeiro cobrada em") then [A] else [B],
SecondColumns = if Text.StartsWith([A],"Taxa de Administração do Agente Financeiro cobrada em") then [B] else [C]]),
    #"Removed Other Columns" = Table.SelectColumns(#"Added Custom",{"CreateNewTable"}),
    #"Expanded CreateNewTable" = Table.ExpandRecordColumn(#"Removed Other Columns", "CreateNewTable", {"FirstColumn", "SecondColumns"}, {"FirstColumn", "SecondColumns"})
in
    #"Expanded CreateNewTable"

    output is this

     

    Copy paste this code to the advanced editor in a new blank query to see how the solution works.

    If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
    Kudoes are nice too

    Have fun

    Jimmy

CNENFRNL's avatar
CNENFRNL
Community Champion
5 years ago

brunofds , you can manipulate the table like building blocks,

  1. truncate the lower part skipping the first 2 rows,
  2. adjust the order of columns for the first 2 rows
  3. combinate 1. and 2.
let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("tVJNT8JAFPwrLz1BrNBSMHBsAE/4kcKNcHi0D7JJu9vsLsSfo/FA8O4v6B9ztyBRATWgt9d0dt68mRmPnRE+ICQEYZIxzpSWWKyKZwGJgHBOXBNcM448JiYFxGIqMUGgDLyg7jXqDa/hOa4TtH3X88FMfJGmzsT9K95aJ2i6QecTczm4TgUuoNqVxTphWthNXUvCixVatLcHvcc5ZmZxic1RxpSigkvzjBtxWiijIoOItJBcHKYIcyE1KRhSLEmjZEdW9Wgp0sX23iPLbu3PiCibilQVT0tiao9sGA56d9C/gcCve35pisX47WYz+IgblGeXxykoHmHnykF1EfHEiLHZ5CmLbS6v5qhdHFuh35r5WyfsLrZxPaIclUKTf06pgJHIzCz/18DTgvfbV63WiVX7GkWvWE8PJXEk2pO4znH6zDL83LyKeV81XxGpOeoyAATjO1czksULj011UEAv7PdB0qwGJi4talaR7dVsIy5+V7DhnbwB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [A = _t, B = _t, C = _t]),
    LowerPart = Table.Skip(Source,2),
    UpperPart = Table.FirstN(Source,2),
    #"Reordered Columns" = Table.ReorderColumns(UpperPart,{"C", "A", "B"}),
    #"Renamed Columns" = Table.RenameColumns(#"Reordered Columns",{{"C", "A"},{"A", "B"},{"B", "C"}}),
    Combination = #"Renamed Columns" & LowerPart
in
    Combination

 

  • brunofds's avatar
    brunofds
    Helper I
    5 years ago

    Dear  CNENFRNL 

     

    Thanks for your time.

     

    I'm afraid your solution won't deal the proper way as new tables are added in the data source

    For instance, the formula 

     LowerPart = Table.Skip(Source,2)

     

    have that number 2 "hardcoded", and will only work within THIS specific scenario i provided.... i'll be adding much more data, so it wont only 2 rows to skip. The same logic applies to 

    UpperPart = Table.FirstN(Source,2),

     

     

    I need to have a dynamic way of computing this new tables been added.

     

    I honestly appreciate your efforts, and found the solution quite clever, although it wont solve my issue, i've learned something new!

    • CNENFRNL's avatar
      CNENFRNL
      Community Champion
      5 years ago

      brunofds , of coz you need to tweak the code according to the scenario,

      LowerPart = Table.Skip(Source, each [A] <> "null"),
UpperPart = Table.FirstN(Source, each [A] <> "null")
      • brunofds's avatar
        brunofds
        Helper I
        5 years ago

        CNENFRNL  Actually i was reading the official documentation after you provided this solution. The null sollution you provided still will be incomplete 

         

        I was thinking of:

         

        Lower part should be something as:

        LowerPart = Table.Skip(Source, each [A] Text.Contains( "Taxa de Administração do Agente Financeiro cobrada")

         

        and UpperPart the opposite:

         

        LowerPart = Table.Skip(Source, each [A] not Text.Contains( "Taxa de Administração do Agente Financeiro cobrada em 03/02/2020")

         

         

        But i cannont insert those kind of text.functions that way.

         

        Any ideas?