Move Columns

[Einomi]

Dear community,

I receive every week simple PDF invoices that are saved to a OneDrive folder. I try to get the relevant data from it in an Excel table. I use Power Query from folder to consolidate. It is a very simple invoice, Qty, Rate, VAT and Amount, this is maximum 5/6 lines. This is a transport company they charge me for pallets.

When I get all invoices, I struggle to get the basic info in a table, I need

Invoice Date

Invoice Number

Qty

Rate

VAT I know there is always 20% so I can add it myself

AMOUNT is the Qty * Rate so I can also figure it out myself

The problem is that the columns are messy, can anyone help with a lead how to structure them

 

@spinfuzer @AlienSx @slorin 

Thanks for your time

[BA_Pete]

Hi @Einomi ,

Re: Your DM - Which part of my description of how to provide sample data are you struggling with?

Let me know where you're getting stuck and we'll work through together.

Pete

[Einomi]

I have made a Group By to have for every invoice the corresponding Table

Basically, we have a pattern, we have the patter of the invoice 5068 - this is the pattern I want for every invoice

And we have the pattern of the invoice 5113 - which I DO NOT want. In this pattern, we need to shift up and move to the right some rows

 

[spinfuzer · Accepted solution]

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("rZldT9swFIb/Coq0O0b9nfiyAi4qbbDRgDQVLlBbaUgVnQrb758hTmqrb4p9zA1JLfRw9NQ+75FZLKqLaXtZnVbT83Z2N2t/udfnv5vNBw+uJ6yZCCak+6C0FNXD6aLiMlj987jZrF+/Ln8/ueeqBPvzvaq7aet+3vhqv1/fXrV5GOE+CHbGvrz9BldnjL0tiKZ7yQCRjAkVoWrZ1VRqDGOzjWGMQsaUNkeMYRDJGHMoO6CMUu8oJoNVijGMzTaGMRoZ0zU7YgyDDo1dXM7Pb2Y/2tn1lft0rF5WR0TTfQfMJIgbzsj97v55Dg/KCD1cna93/56W65fgIB1wdUfrn5haYoHziGg7ImcJFuSoBamErxvTw9XldrM6eXnd7tYn37aPqyMyZO0tDC8J+LHiD1X3xQcviE4Lgmj31kwNQVB0SDE2PwgghnBIMYhmzEYo1XTGmlJjEJtvDGIo0QlBtOg0EcqyriZVaAxj86MTYgwyZhp1LDohiBadduIaXY9qRCffJc1+lRSdEJsfnRBD2GMYVBgaIdHv2ujvlPVdQI9WC4IZ02k9qo5QjR/vdeH+wdj8HgUxQ2hXKK8zQNSuHqAsY31XLzQGsZSuDjAUYxBE6+oyQknf1UWhMYzN7+q4uvwehUE0Y9F2tZp3NZWeSozNN4arIxiDIJIxGbVEW/uLgJR2no/NNoYxhFOJQbTJwZ0/vkdZH4A8WCVNDhCbPzlADMEYBtGM6QClGffGdKkxiM03BjGUWQuCyNNpgBK6M9aUGoNY0nR6iCFOp4egouk0/g6Mnx9FgriU+RHTUzYymU7LwbAbac795Ygt3D8Ym5+DEEPJQQgiz1oBSvBh1iozBrGkWQtURzAGQSUnTkRnmBs/QJjPORMj9JSNTKZTr+Jdl+lRgvP+Kn6/SryKB1jKVTzAUDo2BNGMNRFKqP4OvtAYxOYbgxiKMQgqyrj4OzC2v7b/WFxKCmF6ykZWo/ThP2KYXtR/dEiUfdTVn2MD00WKazNKHy45R+g8gZ5wlwboD/8B", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t, Column4 = _t, Column5 = _t, Column6 = _t, Column7 = _t, #"Invoice Date" = _t, #"Invoice Number" = _t]),
    #"Replaced Value" = Table.ReplaceValue(Source,"null",null,Replacer.ReplaceValue,{"Column1", "Column2", "Column3", "Column4", "Column5", "Column6", "Column7"}),
    #"Changed Type with Locale" = Table.TransformColumnTypes(#"Replaced Value", {{"Invoice Date", type date}}, "en-GB"),
    #"Type modifié" = Table.TransformColumnTypes(#"Changed Type with Locale",{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}, {"Column5", type text}, {"Column6", type text}, {"Column7", type text}, {"Invoice Date", type date}, {"Invoice Number", Int64.Type}}),
    #"Grouped Rows" = Table.Group(
        #"Type modifié", 
        {"Invoice Date", "Invoice Number"}, 
        {{"Rows", each 
        
        let
            remove = Table.RemoveColumns(_,{"Invoice Date","Invoice Number"}),
            transform = if remove[Column3]{0} <> null 
                then Table.PromoteHeaders(remove) 
                else [ split_pos = List.PositionOf(remove[Column1],"QTY"),
                    left = Table.PromoteHeaders(Table.FirstN(remove,split_pos)),
                    left_final = Table.RemoveColumns(left,List.Select(Table.ColumnNames(left), (x) => Text.StartsWith(x,"Column"))),
                    right = Table.PromoteHeaders(Table.LastN(remove,split_pos)),
                    right_final = Table.RemoveColumns(right,List.Select(Table.ColumnNames(right), (x) => Text.StartsWith(x,"Column"))),
                    combine = Table.FromColumns(Table.ToColumns(left_final) & Table.ToColumns(right_final),
                        Table.ColumnNames(left_final & right_final))
                ][combine]
        in
            transform
            
            
        }}
        
    ),
    #"Expanded Rows" = Table.ExpandTableColumn(#"Grouped Rows", "Rows", {"DATE", "ACTIVITY", "DESCRIPTION", "QTY", "VAT", "RATE", "AMOUNT"}, {"DATE", "ACTIVITY", "DESCRIPTION", "QTY", "VAT", "RATE", "AMOUNT"})
in
    #"Expanded Rows"

[Einomi]

Thanks @spinfuzer , so far it works great.

I am sure you are busy, but if you manage to explain with the big lines how did you approach the issue and what are the majors steps to resolve it, would be greatly appreciated

[spinfuzer]

Some of the invoices are perfect and there are others that need to be moved.  So we want to group the invoices first using operation all rows.  Now you can edit the formula and change the each _ to each (custom formula) in the Table.Group formula.

Test if Column 3 is null, if so, then just promote headers.  Otherwise we need to split the table and move the columns.

In Column 1, look for the position "QTY" value.  Take this position and split the top/bottom tables.

Next remove the null columns by checking for column name starts with "Column".

Now combine the two tables by turning them into columns and then using Table.FromColumns.

[Einomi]

Hi Pete,

I have tried to understand your message, I got it !

Here is the code

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("rZldT9swFIb/Coq0O0b9nfiyAi4qbbDRgDQVLlBbaUgVnQrb758hTmqrb4p9zA1JLfRw9NQ+75FZLKqLaXtZnVbT83Z2N2t/udfnv5vNBw+uJ6yZCCak+6C0FNXD6aLiMlj987jZrF+/Ln8/ueeqBPvzvaq7aet+3vhqv1/fXrV5GOE+CHbGvrz9BldnjL0tiKZ7yQCRjAkVoWrZ1VRqDGOzjWGMQsaUNkeMYRDJGHMoO6CMUu8oJoNVijGMzTaGMRoZ0zU7YgyDDo1dXM7Pb2Y/2tn1lft0rF5WR0TTfQfMJIgbzsj97v55Dg/KCD1cna93/56W65fgIB1wdUfrn5haYoHziGg7ImcJFuSoBamErxvTw9XldrM6eXnd7tYn37aPqyMyZO0tDC8J+LHiD1X3xQcviE4Lgmj31kwNQVB0SDE2PwgghnBIMYhmzEYo1XTGmlJjEJtvDGIo0QlBtOg0EcqyriZVaAxj86MTYgwyZhp1LDohiBadduIaXY9qRCffJc1+lRSdEJsfnRBD2GMYVBgaIdHv2ujvlPVdQI9WC4IZ02k9qo5QjR/vdeH+wdj8HgUxQ2hXKK8zQNSuHqAsY31XLzQGsZSuDjAUYxBE6+oyQknf1UWhMYzN7+q4uvwehUE0Y9F2tZp3NZWeSozNN4arIxiDIJIxGbVEW/uLgJR2no/NNoYxhFOJQbTJwZ0/vkdZH4A8WCVNDhCbPzlADMEYBtGM6QClGffGdKkxiM03BjGUWQuCyNNpgBK6M9aUGoNY0nR6iCFOp4egouk0/g6Mnx9FgriU+RHTUzYymU7LwbAbac795Ygt3D8Ym5+DEEPJQQgiz1oBSvBh1iozBrGkWQtURzAGQSUnTkRnmBs/QJjPORMj9JSNTKZTr+Jdl+lRgvP+Kn6/SryKB1jKVTzAUDo2BNGMNRFKqP4OvtAYxOYbgxiKMQgqyrj4OzC2v7b/WFxKCmF6ykZWo/ThP2KYXtR/dEiUfdTVn2MD00WKazNKHy45R+g8gZ5wlwboD/8B", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t, Column4 = _t, Column5 = _t, Column6 = _t, Column7 = _t, #"Invoice Date" = _t, #"Invoice Number" = _t]),
    #"Type modifié" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}, {"Column5", type text}, {"Column6", type text}, {"Column7", type text}, {"Invoice Date", type date}, {"Invoice Number", Int64.Type}})
in
    #"Type modifié"

I want to achieve what I put in the red square for every invoice, meaning having 9 columns for every invoice

[BA_Pete]

Hi @Einomi ,

Can you provide usable sample data please?

In this instance, I would recommend that you copy the table in Power Query for one of your imported invoices by using the the Copy Entire Table option in the dropdown at the top left of your PQ table, then paste this into the Enter Data option on the Home tab ribbon, then open Advanced Editor for this new query and copy all of the M code and paste it into a code window ( </> button) here.

Pete