Forum Discussion
Einomi
Helper V
Helper VMove 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 consolidat...
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"
EinomiHelper V
Helper VI 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
Solution Sage
Solution Sage
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.