Forum Discussion

BardiMobor's avatar
BardiMobor
New Member
3 years ago

6 columns to 3

Hi everyone

i have a lot of PDF files that i need to import in Excel. The PDFs were made by printing in landscape mode, so there are 2 pages on each sheet.

So when i import in in power query i have 6 columns (ID, description, price, ID_1, description_1, price_1).

So i need that all the content in the last 3 columns (ID_1, description_1, price_1) must be queued on the first 3.

 

Any smart way to do this?

tnx all

Need Help

3 Replies

  • m_dekorte's avatar
    m_dekorte
    Resident Rockstar
    3 years ago

    Hi BardiMobor,

     

    If that structure is fixed like you've described it can easily be coded, here's my sample data

     

    create a list with unique column names

     

    And transform the sample data

     

    Here's the M code, you can copy this into a new blank query

    let
    Cols = List.Distinct( 
        List.Transform( Table.ColumnNames( Source ), each Text.Remove(_, { "_", "0".."9"}))
    ),
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WckosSslU0lEKcHFTSM5IzMlJzUtPBfINgdg3Pym/CEj75SuUJ1YCGUZKsbEA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, Description = _t, Price = _t, ID_1 = _t, Description_1 = _t, Price_1 = _t]),
    Transform = Table.Combine( 
        List.Transform( 
            List.Split( Table.ToColumns( Source ), 3), 
            each Table.FromColumns(_, Cols)
        )
    )
in
    Transform

     

     

    Ps. If this helps solve your query please mark this post as Solution, thanks!

  • BardiMobor's avatar
    BardiMobor
    New Member
    3 years ago

    thank you m_dekorte ,

    this is exactly what i need.

    I still figuring out how to apply to my Queries tho, becouse i had to do some data cleanup before get the initial structure like you posted (image1)

    • m_dekorte's avatar
      m_dekorte
      Resident Rockstar
      3 years ago

      I'm sure we can figure that out BardiMobor 

      See if these instructions help with implementing it in your query.

       

      Go to your Query and open the Advanced Editor

      Copy the first two variables, Cols and SourceNew directly below the let-statement

      Now replace ENTER_YOUR_PREVIOUS_STEP_NAME_HERE with the name that follows after the in-clause in your query. Note that there is and needs to be a comma at the end of this line once you've done that.

       

      Now copy everything from the Transform variable all the way to the end (that's the Transform after the in-clause), go back to your query and select the in-clause and all that follows after that - paste this in its place.

      Done!

       

      let
          Cols = List.Distinct( List.Transform( Table.ColumnNames( SourceNew ), each Text.Remove(_, { "_", "0".."9"})) ),
          SourceNew = ENTER_YOUR_PREVIOUS_STEP_NAME_HERE,
          Transform = Table.Combine(
              List.Transform(
                  List.Split( Table.ToColumns( SourceNew ), 3),
                  each Table.FromColumns(_, Cols)
              ))
      in
          Transform
       
       
      Ps. If this helps solve your query please mark this post as Solution, thanks!