Forum Discussion

Einomi's avatar
Einomi
Helper V
2 years ago
Solved

Move Date after GroupBy

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)


m_dekorte spinfuzer 

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

    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"

     

     

1 Reply

  • spinfuzer's avatar
    spinfuzer
    Solution Sage
    2 years ago

    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"