6 columns to 3

[BardiMobor]

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

[BardiMobor]

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]

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!

[m_dekorte]

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!