Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello,
I have once again PDF invoices coming through, unfortunately even if they appear the same, in Power Qeury they look different
I will paste the code for your convenience as @BA_Pete taught me (thanks for that)
I have 12 columns.
The first one is Source.Name and I want to keep it as is
The next 11 columns need a bit tranformation, what I need is
Col 1 : Item Code
Col 2 : Qty
Col 6 : Special Offer
Col 7 : Price
Col 8 : %
Col 9 : Disc
Col 10 : VAT
Col 11 : Subtotal
Everything between Col 3 and Col 5 will be merge and will be the description. Eventually we will end un with less columns that 11
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("zVptc5s4EP4rmsx0xunkiCTeP9rEaTNpGjfkcnPT6wdqKzZjAj5eEre//iSQjMBgSGr7+iHBO8Y8u6vV7rOLvn49ufr8gFTL1G1lNXs8OTu5Cp8jf0rYJ/EN/RxmQfDmy7ezbZgLL2UYWD9H8BxDrB4ChNsC7qO2n16QwH8m8Y47+kKl5Ak40YwZ9SX9kT86mcb+KvWjsO1pk7jw9Dt2u59M6eVheE//u9n3NEq9ZqiHMbToBSkQsltfCEmnURwCR7lVHIDw2obmvA0Sa4rFfjaA4N2pJMPiaYXYjIrzG4r7brJkEUfRUwLcgNowAxpYA6zoSwl4eHdxyxRckanvBeD28ZHETG+reITQQMhcA9Vg12YNLLu0exjPIjAhqZ+CSeQnAME1um43W1XMitlcFmbn4v+8rsNPEDEHcPuCVRyBfHVD6t/b2J/7IXWjG/3wwI0fLMFgFGTkFFhrFID3KwYJ/HAaEy8hbYohTcGG7HsuczdoWDGt5u06dnQ19z6/dTT3QuCuvDlJUx/gNYLB9y24+tJjWF16IYtVgLBt7Sm+kXuHr/41jflFtgLuvxkhPwlAxjnWop9gPCOtq6JqiiajC1kEXi42gV9fuBC5kvETj7p76RWrA9CyNexQzWBUNRi1G8wwVRnTXXgvXvzkgYQucBT64XwXsF011a5YamttqO7HoaFLu5zGE429D15MNzmLj7XaAKnrii6DCVkYiVArnOt8lFfV8ZYE3Hjx3Iv9kABtrbdbaOCqa4XMUQtxA2qbNM0cpyjIUP2SR3e66IZ6GKPC21J2dNPYe/lO4tgnCRiMiwDkWVKDiqlL7hOyyARc7IbFqIwXx4vjKN3UhJaChEzFkKGFLPahrqiwF7QqhQ7LhcJUJwtS/5kSjBkFp3VJh3AO3MVL+VBNq4askIX1XOxWwbBK63OfD1ergFAHzBT2+HEWR6dlaVK1aswKWcQsr4TdsKZEAK6+XIJRlqYkDrOUJUQvWVDI5RwM/iZenATkx6nk/N3VF/VW4TW1uFf1/b220xAhvTTQjWitmUQvM5oVBPVhiXQNqJ1ggJCGit21le8bE34zvgahoR4nSVWgOrz6WnfugKLMVS33y4eYkBCMiBcm4JLl+78WEd07ImvQjcvyBucNqpwxhCwlq7pTd2qRxy7uiN2OoNXMoqb+fl7mPNLaD4/soIw09JW8HempHCOSebOnSTxy4iUpi/UnL42AwUgGeP9+cnfljMHVZ+duPHTHAKlSp6hb1S5GyJuEoiu4c3GQfaQ+sQJ12BwmQ1FXY60M9NzToziaTqPAB5dBFBNaqgVxHhgWnMfMl3XSbtDyXCFaXBYB0OTrNnWMmjqOlwX+YxC9UNceT5OHsY1HLGp4HooC2jUwlssuooBT/0vJYKuJ1WuJXq/keVPfleZV+0hctAJ16MAroSgpxCdl7VzRfDNdAGcRrVaMksE161zAaPzh7rZ8GjIUXGGFXBaLy8VubEPiRr9MyYT4G3mXp3e8n/TebzCALUXHPXQT/SNq6R9pTS86yI6eUYidqz1EUi1xFv50SbdwztZu/DW48OYkJ8NrFnDXYRTH5UN1s7ryQhYm4x1UuKqDxiKOj22cKEum9A/cEboKCMo0WMVFZ7OJNS6LttxQ7HZA3a5xw5Oz8ptX5IaOhFHC8Akt1M4R3uuEtsGWwyVACerQDKyEmjgW26K8CIwIeaTA02Xq+UHC+I0Gc4JpVFtwIW/Kmbrdg9dBEUbNg/v8m72tmATDw4LRsT2HxbYtBwsLGeqwA14Z6q3tT6MKvXqiZi1wfVA0+vPTtRjabCb7JZS2rk32cW18gqvjk0JsxlalEffrZjbVS8cEh22eFh2OuOAHKdXVS6/C3biPRSMmBmjNjVgbbkcHVogtuMUkXzvYJB+peWPQhN6boNSs7UNXGgN+iKA+KkP+3lvQNQfIYgT4nwxCbKbe0xZevd0wa+2Gqde4ii5Pu2sKvJkh1XzQiy9JOtCM1FKZ2nrzN2XyDcwhK1PdlgNWphLqwIRFghrdf0SwzFRONs2eaGZOaPoBI/Yu5gwYxh8WBJTahikYXEbRDLgkfqYanAK8tj+d5Pyl+nbPqASIjrf5dIdKrOXgkT7xU1YbiqJ5yzyctKih12awenUEi/WGEeyWHlbLeYj8m/3FlbV1HsLcf/Bu2XK44LV6vzf/5eC19Pqcy3jdnMvSqvNdIYtwhVb3DFHSgpI7abpVvAkDFzmlyicdSvke7A0vwHYD9zoO0uvkxytQX/Hib78GHzG0OIHTjjJKZwVV669br1MJ/Q4g9MbcdT4A9J7u8BMB/VHFMYFNtDUeE+g4EYBwwyxvV4QXzImzHE6UppxAJYxAPVECxZnTAGk2lllRC0tSbbtdiW//AQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Source.Name = _t, Column1 = _t, Column2 = _t, Column3 = _t, Column4 = _t, Column5 = _t, Column6 = _t, Column7 = _t, Column8 = _t, Column9 = _t, Column10 = _t, Column11 = _t]),
#"Type modifié" = Table.TransformColumnTypes(Source,{{"Source.Name", type text}, {"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}, {"Column5", type text}, {"Column6", type text}, {"Column7", type text}, {"Column8", type text}, {"Column9", type text}, {"Column10", type text}, {"Column11", type text}}),
#"Lignes groupées" = Table.Group(#"Type modifié", {"Source.Name"}, {{"Tables", each _, type table [Source.Name=nullable text, Column1=nullable text, Column2=nullable text, Column3=nullable text, Column4=nullable text, Column5=nullable text, Column6=nullable text, Column7=nullable text, Column8=nullable text, Column9=nullable text, Column10=nullable text, Column11=nullable text]}})
in
#"Lignes groupées"
Thanks for your time and for your help (if possible to tell me your approach so that I can learn also)
Solved! Go to Solution.
You can read the step names and it should give you a good idea of the approach. I removed the column names/types from the Group function because it will mess up the Table Expand when the column names are not properly defined. I combined Special Offer with the description. You can move it to a separate column or add a custom column with if Text.Contains([Description], "Special Offer") then "something" else "something else"
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("zVptc5s4EP4rmsx0xunkiCTeP9rEaTNpGjfkcnPT6wdqKzZjAj5eEre//iSQjMBgSGr7+iHBO8Y8u6vV7rOLvn49ufr8gFTL1G1lNXs8OTu5Cp8jf0rYJ/EN/RxmQfDmy7ezbZgLL2UYWD9H8BxDrB4ChNsC7qO2n16QwH8m8Y47+kKl5Ak40YwZ9SX9kT86mcb+KvWjsO1pk7jw9Dt2u59M6eVheE//u9n3NEq9ZqiHMbToBSkQsltfCEmnURwCR7lVHIDw2obmvA0Sa4rFfjaA4N2pJMPiaYXYjIrzG4r7brJkEUfRUwLcgNowAxpYA6zoSwl4eHdxyxRckanvBeD28ZHETG+reITQQMhcA9Vg12YNLLu0exjPIjAhqZ+CSeQnAME1um43W1XMitlcFmbn4v+8rsNPEDEHcPuCVRyBfHVD6t/b2J/7IXWjG/3wwI0fLMFgFGTkFFhrFID3KwYJ/HAaEy8hbYohTcGG7HsuczdoWDGt5u06dnQ19z6/dTT3QuCuvDlJUx/gNYLB9y24+tJjWF16IYtVgLBt7Sm+kXuHr/41jflFtgLuvxkhPwlAxjnWop9gPCOtq6JqiiajC1kEXi42gV9fuBC5kvETj7p76RWrA9CyNexQzWBUNRi1G8wwVRnTXXgvXvzkgYQucBT64XwXsF011a5YamttqO7HoaFLu5zGE429D15MNzmLj7XaAKnrii6DCVkYiVArnOt8lFfV8ZYE3Hjx3Iv9kABtrbdbaOCqa4XMUQtxA2qbNM0cpyjIUP2SR3e66IZ6GKPC21J2dNPYe/lO4tgnCRiMiwDkWVKDiqlL7hOyyARc7IbFqIwXx4vjKN3UhJaChEzFkKGFLPahrqiwF7QqhQ7LhcJUJwtS/5kSjBkFp3VJh3AO3MVL+VBNq4askIX1XOxWwbBK63OfD1ergFAHzBT2+HEWR6dlaVK1aswKWcQsr4TdsKZEAK6+XIJRlqYkDrOUJUQvWVDI5RwM/iZenATkx6nk/N3VF/VW4TW1uFf1/b220xAhvTTQjWitmUQvM5oVBPVhiXQNqJ1ggJCGit21le8bE34zvgahoR4nSVWgOrz6WnfugKLMVS33y4eYkBCMiBcm4JLl+78WEd07ImvQjcvyBucNqpwxhCwlq7pTd2qRxy7uiN2OoNXMoqb+fl7mPNLaD4/soIw09JW8HempHCOSebOnSTxy4iUpi/UnL42AwUgGeP9+cnfljMHVZ+duPHTHAKlSp6hb1S5GyJuEoiu4c3GQfaQ+sQJ12BwmQ1FXY60M9NzToziaTqPAB5dBFBNaqgVxHhgWnMfMl3XSbtDyXCFaXBYB0OTrNnWMmjqOlwX+YxC9UNceT5OHsY1HLGp4HooC2jUwlssuooBT/0vJYKuJ1WuJXq/keVPfleZV+0hctAJ16MAroSgpxCdl7VzRfDNdAGcRrVaMksE161zAaPzh7rZ8GjIUXGGFXBaLy8VubEPiRr9MyYT4G3mXp3e8n/TebzCALUXHPXQT/SNq6R9pTS86yI6eUYidqz1EUi1xFv50SbdwztZu/DW48OYkJ8NrFnDXYRTH5UN1s7ryQhYm4x1UuKqDxiKOj22cKEum9A/cEboKCMo0WMVFZ7OJNS6LttxQ7HZA3a5xw5Oz8ptX5IaOhFHC8Akt1M4R3uuEtsGWwyVACerQDKyEmjgW26K8CIwIeaTA02Xq+UHC+I0Gc4JpVFtwIW/Kmbrdg9dBEUbNg/v8m72tmATDw4LRsT2HxbYtBwsLGeqwA14Z6q3tT6MKvXqiZi1wfVA0+vPTtRjabCb7JZS2rk32cW18gqvjk0JsxlalEffrZjbVS8cEh22eFh2OuOAHKdXVS6/C3biPRSMmBmjNjVgbbkcHVogtuMUkXzvYJB+peWPQhN6boNSs7UNXGgN+iKA+KkP+3lvQNQfIYgT4nwxCbKbe0xZevd0wa+2Gqde4ii5Pu2sKvJkh1XzQiy9JOtCM1FKZ2nrzN2XyDcwhK1PdlgNWphLqwIRFghrdf0SwzFRONs2eaGZOaPoBI/Yu5gwYxh8WBJTahikYXEbRDLgkfqYanAK8tj+d5Pyl+nbPqASIjrf5dIdKrOXgkT7xU1YbiqJ5yzyctKih12awenUEi/WGEeyWHlbLeYj8m/3FlbV1HsLcf/Bu2XK44LV6vzf/5eC19Pqcy3jdnMvSqvNdIYtwhVb3DFHSgpI7abpVvAkDFzmlyicdSvke7A0vwHYD9zoO0uvkxytQX/Hib78GHzG0OIHTjjJKZwVV669br1MJ/Q4g9MbcdT4A9J7u8BMB/VHFMYFNtDUeE+g4EYBwwyxvV4QXzImzHE6UppxAJYxAPVECxZnTAGk2lllRC0tSbbtdiW//AQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Source.Name = _t, Column1 = _t, Column2 = _t, Column3 = _t, Column4 = _t, Column5 = _t, Column6 = _t, Column7 = _t, Column8 = _t, Column9 = _t, Column10 = _t, Column11 = _t]),
#"Type modifié" = Table.TransformColumnTypes(Source,{{"Source.Name", type text}, {"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}, {"Column5", type text}, {"Column6", type text}, {"Column7", type text}, {"Column8", type text}, {"Column9", type text}, {"Column10", type text}, {"Column11", type text}}),
#"Replaced Value" = Table.ReplaceValue(#"Type modifié","null",null,Replacer.ReplaceValue,{"Source.Name", "Column1", "Column2", "Column3", "Column4", "Column5", "Column6", "Column7", "Column8", "Column9", "Column10", "Column11"}),
transform_table = (tbl as table) =>
let
filter_nulls = Table.SelectRows(tbl, each [Column3] <> null),
headers = Table.PromoteHeaders(filter_nulls),
filter_duplicate_headers = Table.SelectRows(headers, each [Qty] <> "Qty"),
merge_col_names = List.Select(Table.ColumnNames(filter_duplicate_headers), each Text.StartsWith(_,"Column")),
replace_null_with_blanks = Table.ReplaceValue(filter_duplicate_headers,null,"",Replacer.ReplaceValue,merge_col_names),
remove_first_col = Table.RemoveColumns(replace_null_with_blanks, Table.ColumnNames(replace_null_with_blanks){0}),
merge_columns = Table.CombineColumns(remove_first_col, {"Description"} & merge_col_names,Combiner.CombineTextByDelimiter(" ", QuoteStyle.None),"Description")
in
merge_columns,
#"Lignes groupées" = Table.Group(#"Replaced Value", {"Source.Name"}, {{"Tables", each transform_table(_)}}),
#"Expanded Tables" = Table.ExpandTableColumn(#"Lignes groupées", "Tables", {"Item Code", "Qty", "Description", "Price", "%", "Disc", "VAT", "Subtotal"}, {"Item Code", "Qty", "Description", "Price", "%", "Disc", "VAT", "Subtotal"})
in
#"Expanded Tables"
You can read the step names and it should give you a good idea of the approach. I removed the column names/types from the Group function because it will mess up the Table Expand when the column names are not properly defined. I combined Special Offer with the description. You can move it to a separate column or add a custom column with if Text.Contains([Description], "Special Offer") then "something" else "something else"
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("zVptc5s4EP4rmsx0xunkiCTeP9rEaTNpGjfkcnPT6wdqKzZjAj5eEre//iSQjMBgSGr7+iHBO8Y8u6vV7rOLvn49ufr8gFTL1G1lNXs8OTu5Cp8jf0rYJ/EN/RxmQfDmy7ezbZgLL2UYWD9H8BxDrB4ChNsC7qO2n16QwH8m8Y47+kKl5Ak40YwZ9SX9kT86mcb+KvWjsO1pk7jw9Dt2u59M6eVheE//u9n3NEq9ZqiHMbToBSkQsltfCEmnURwCR7lVHIDw2obmvA0Sa4rFfjaA4N2pJMPiaYXYjIrzG4r7brJkEUfRUwLcgNowAxpYA6zoSwl4eHdxyxRckanvBeD28ZHETG+reITQQMhcA9Vg12YNLLu0exjPIjAhqZ+CSeQnAME1um43W1XMitlcFmbn4v+8rsNPEDEHcPuCVRyBfHVD6t/b2J/7IXWjG/3wwI0fLMFgFGTkFFhrFID3KwYJ/HAaEy8hbYohTcGG7HsuczdoWDGt5u06dnQ19z6/dTT3QuCuvDlJUx/gNYLB9y24+tJjWF16IYtVgLBt7Sm+kXuHr/41jflFtgLuvxkhPwlAxjnWop9gPCOtq6JqiiajC1kEXi42gV9fuBC5kvETj7p76RWrA9CyNexQzWBUNRi1G8wwVRnTXXgvXvzkgYQucBT64XwXsF011a5YamttqO7HoaFLu5zGE429D15MNzmLj7XaAKnrii6DCVkYiVArnOt8lFfV8ZYE3Hjx3Iv9kABtrbdbaOCqa4XMUQtxA2qbNM0cpyjIUP2SR3e66IZ6GKPC21J2dNPYe/lO4tgnCRiMiwDkWVKDiqlL7hOyyARc7IbFqIwXx4vjKN3UhJaChEzFkKGFLPahrqiwF7QqhQ7LhcJUJwtS/5kSjBkFp3VJh3AO3MVL+VBNq4askIX1XOxWwbBK63OfD1ergFAHzBT2+HEWR6dlaVK1aswKWcQsr4TdsKZEAK6+XIJRlqYkDrOUJUQvWVDI5RwM/iZenATkx6nk/N3VF/VW4TW1uFf1/b220xAhvTTQjWitmUQvM5oVBPVhiXQNqJ1ggJCGit21le8bE34zvgahoR4nSVWgOrz6WnfugKLMVS33y4eYkBCMiBcm4JLl+78WEd07ImvQjcvyBucNqpwxhCwlq7pTd2qRxy7uiN2OoNXMoqb+fl7mPNLaD4/soIw09JW8HempHCOSebOnSTxy4iUpi/UnL42AwUgGeP9+cnfljMHVZ+duPHTHAKlSp6hb1S5GyJuEoiu4c3GQfaQ+sQJ12BwmQ1FXY60M9NzToziaTqPAB5dBFBNaqgVxHhgWnMfMl3XSbtDyXCFaXBYB0OTrNnWMmjqOlwX+YxC9UNceT5OHsY1HLGp4HooC2jUwlssuooBT/0vJYKuJ1WuJXq/keVPfleZV+0hctAJ16MAroSgpxCdl7VzRfDNdAGcRrVaMksE161zAaPzh7rZ8GjIUXGGFXBaLy8VubEPiRr9MyYT4G3mXp3e8n/TebzCALUXHPXQT/SNq6R9pTS86yI6eUYidqz1EUi1xFv50SbdwztZu/DW48OYkJ8NrFnDXYRTH5UN1s7ryQhYm4x1UuKqDxiKOj22cKEum9A/cEboKCMo0WMVFZ7OJNS6LttxQ7HZA3a5xw5Oz8ptX5IaOhFHC8Akt1M4R3uuEtsGWwyVACerQDKyEmjgW26K8CIwIeaTA02Xq+UHC+I0Gc4JpVFtwIW/Kmbrdg9dBEUbNg/v8m72tmATDw4LRsT2HxbYtBwsLGeqwA14Z6q3tT6MKvXqiZi1wfVA0+vPTtRjabCb7JZS2rk32cW18gqvjk0JsxlalEffrZjbVS8cEh22eFh2OuOAHKdXVS6/C3biPRSMmBmjNjVgbbkcHVogtuMUkXzvYJB+peWPQhN6boNSs7UNXGgN+iKA+KkP+3lvQNQfIYgT4nwxCbKbe0xZevd0wa+2Gqde4ii5Pu2sKvJkh1XzQiy9JOtCM1FKZ2nrzN2XyDcwhK1PdlgNWphLqwIRFghrdf0SwzFRONs2eaGZOaPoBI/Yu5gwYxh8WBJTahikYXEbRDLgkfqYanAK8tj+d5Pyl+nbPqASIjrf5dIdKrOXgkT7xU1YbiqJ5yzyctKih12awenUEi/WGEeyWHlbLeYj8m/3FlbV1HsLcf/Bu2XK44LV6vzf/5eC19Pqcy3jdnMvSqvNdIYtwhVb3DFHSgpI7abpVvAkDFzmlyicdSvke7A0vwHYD9zoO0uvkxytQX/Hib78GHzG0OIHTjjJKZwVV669br1MJ/Q4g9MbcdT4A9J7u8BMB/VHFMYFNtDUeE+g4EYBwwyxvV4QXzImzHE6UppxAJYxAPVECxZnTAGk2lllRC0tSbbtdiW//AQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Source.Name = _t, Column1 = _t, Column2 = _t, Column3 = _t, Column4 = _t, Column5 = _t, Column6 = _t, Column7 = _t, Column8 = _t, Column9 = _t, Column10 = _t, Column11 = _t]),
#"Type modifié" = Table.TransformColumnTypes(Source,{{"Source.Name", type text}, {"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}, {"Column5", type text}, {"Column6", type text}, {"Column7", type text}, {"Column8", type text}, {"Column9", type text}, {"Column10", type text}, {"Column11", type text}}),
#"Replaced Value" = Table.ReplaceValue(#"Type modifié","null",null,Replacer.ReplaceValue,{"Source.Name", "Column1", "Column2", "Column3", "Column4", "Column5", "Column6", "Column7", "Column8", "Column9", "Column10", "Column11"}),
transform_table = (tbl as table) =>
let
filter_nulls = Table.SelectRows(tbl, each [Column3] <> null),
headers = Table.PromoteHeaders(filter_nulls),
filter_duplicate_headers = Table.SelectRows(headers, each [Qty] <> "Qty"),
merge_col_names = List.Select(Table.ColumnNames(filter_duplicate_headers), each Text.StartsWith(_,"Column")),
replace_null_with_blanks = Table.ReplaceValue(filter_duplicate_headers,null,"",Replacer.ReplaceValue,merge_col_names),
remove_first_col = Table.RemoveColumns(replace_null_with_blanks, Table.ColumnNames(replace_null_with_blanks){0}),
merge_columns = Table.CombineColumns(remove_first_col, {"Description"} & merge_col_names,Combiner.CombineTextByDelimiter(" ", QuoteStyle.None),"Description")
in
merge_columns,
#"Lignes groupées" = Table.Group(#"Replaced Value", {"Source.Name"}, {{"Tables", each transform_table(_)}}),
#"Expanded Tables" = Table.ExpandTableColumn(#"Lignes groupées", "Tables", {"Item Code", "Qty", "Description", "Price", "%", "Disc", "VAT", "Subtotal"}, {"Item Code", "Qty", "Description", "Price", "%", "Disc", "VAT", "Subtotal"})
in
#"Expanded Tables"
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.