Forum Discussion

Einomi's avatar
Einomi
Helper V
2 years ago
Solved

Move Columns

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

General Comment
Need Help
Show and Tell
Tips and Tricks
Tutorial Requests
  • spinfuzer's avatar
    spinfuzer
    2 years ago

     

     

    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"

     

7 Replies

  • BA_Pete's avatar
    BA_Pete
    Super User
    2 years ago

    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's avatar
      Einomi
      Helper V
      2 years ago

      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

       

       

    • Einomi's avatar
      Einomi
      Helper V
      2 years ago

      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's avatar
        spinfuzer
        Solution Sage
        2 years ago

         

         

        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"

         

  • BA_Pete's avatar
    BA_Pete
    Super User
    2 years ago

    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