Forum Discussion
Moving values from columns
Hello there
I'm struggling with this silly issue in PQ.
I have multiples pdfs tables as a data source.
Due to the nature of these pdfs tables, after some work in PQ, the result I got is somehow like this:
| A | B | C |
| Taxa de Administração do Agente Financeiro cobrada em 03/02/2020 | 381,01 | null |
| Taxa de Administração do Agente Financeiro cobrada em 03/02/2020 | 3.934,39 | null |
| null | ( + )Crédito de Cobrança | 0 |
| null | ( + )Pagamento de parcelas - Contratos com Retorno | 0 |
| null | ( + )Aportes Secretaria | 0 |
| null | ( + )Devolução de parcelas - Contratos Não Reembolsáveis | 0 |
| null | SALDO EM 31/01/2020 | 18443 |
| null | Lançamentos à Crédito | 0 |
| null | ( + )Rendas de Aplicações Financeiras - Cobrança | 0 |
| null | ( + )Aportes Secretaria | 0 |
| null | ( + )Rendimento Repassado pelo Tomador | 0 |
| null | ( + )Devolução de parcelas - Contratos Não Reembolsáveis | 0 |
| null | ( + )Pagamento de parcelas - Contratos com Retorno | 18655 |
| null | ( + )Crédito de Cobrança | 0 |
| null | Lançamentos à Débito | 0 |
| null | SALDO EM 31/01/2020 | 0 |
| null | Lançamentos à Débito | 0 |
| null | ( + )Rendimento Repassado pelo Tomador | 0 |
| null | ( + )Rendas de Aplicações Financeiras - Cobrança | 0 |
| null | Lançamentos à Crédito | 0 |
| ( - ) | Resgate para transferência ao DAEE ref. Pagto. de tarifas de cobrança | 0 |
This is the basic strutucture, so there will always be this specific text: "Taxa de Administração do Agente Financeiro cobrada em". So in this case I need to move the values from "A" to "B" and from "B" to "C".
My desired output is (note that there are changes only in the first 2 rows) :
| A | B | C |
| null | Taxa de Administração do Agente Financeiro cobrada em 03/02/2020 | 381,01 |
| null | Taxa de Administração do Agente Financeiro cobrada em 03/02/2020 | 3.934,39 |
| null | ( + )Crédito de Cobrança | 0 |
| null | ( + )Pagamento de parcelas - Contratos com Retorno | 0 |
| null | ( + )Aportes Secretaria | 0 |
| null | ( + )Devolução de parcelas - Contratos Não Reembolsáveis | 0 |
| null | SALDO EM 31/01/2020 | 18443 |
| null | Lançamentos à Crédito | 0 |
| null | ( + )Rendas de Aplicações Financeiras - Cobrança | 0 |
| null | ( + )Aportes Secretaria | 0 |
| null | ( + )Rendimento Repassado pelo Tomador | 0 |
| null | ( + )Devolução de parcelas - Contratos Não Reembolsáveis | 0 |
| null | ( + )Pagamento de parcelas - Contratos com Retorno | 18655 |
| null | ( + )Crédito de Cobrança | 0 |
| null | Lançamentos à Débito | 0 |
| null | SALDO EM 31/01/2020 | 0 |
| null | Lançamentos à Débito | 0 |
| null | ( + )Rendimento Repassado pelo Tomador | 0 |
| null | ( + )Rendas de Aplicações Financeiras - Cobrança | 0 |
| null | Lançamentos à Crédito | 0 |
| ( - ) | Resgate para transferência ao DAEE ref. Pagto. de tarifas de cobrança | 0 |
Thank you.
Hello brunofds
add a new column where you are generating your new table. Then delete all other columns and expand the new columns
here the formula
[FirstColumn = if Text.StartsWith([A],"Taxa de Administração do Agente Financeiro cobrada em") then [A] else [B], SecondColumns = if Text.StartsWith([A],"Taxa de Administração do Agente Financeiro cobrada em") then [B] else [C]]here the complete code
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("tVJNT8JAFPwrLz1BrNBSMHBsAE/4kcKNcHi0D7JJu9vsLsSfo/FA8O4v6B9ztyBRATWgt9d0dt68mRmPnRE+ICQEYZIxzpSWWKyKZwGJgHBOXBNcM448JiYFxGIqMUGgDLyg7jXqDa/hOa4TtH3X88FMfJGmzsT9K95aJ2i6QecTczm4TgUuoNqVxTphWthNXUvCixVatLcHvcc5ZmZxic1RxpSigkvzjBtxWiijIoOItJBcHKYIcyE1KRhSLEmjZEdW9Wgp0sX23iPLbu3PiCibilQVT0tiao9sGA56d9C/gcCve35pisX47WYz+IgblGeXxykoHmHnykF1EfHEiLHZ5CmLbS6v5qhdHFuh35r5WyfsLrZxPaIclUKTf06pgJHIzCz/18DTgvfbV63WiVX7GkWvWE8PJXEk2pO4znH6zDL83LyKeV81XxGpOeoyAATjO1czksULj011UEAv7PdB0qwGJi4talaR7dVsIy5+V7DhnbwB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [A = _t, B = _t, C = _t]), #"Added Custom" = Table.AddColumn(Source, "CreateNewTable", each [FirstColumn = if Text.StartsWith([A],"Taxa de Administração do Agente Financeiro cobrada em") then [A] else [B], SecondColumns = if Text.StartsWith([A],"Taxa de Administração do Agente Financeiro cobrada em") then [B] else [C]]), #"Removed Other Columns" = Table.SelectColumns(#"Added Custom",{"CreateNewTable"}), #"Expanded CreateNewTable" = Table.ExpandRecordColumn(#"Removed Other Columns", "CreateNewTable", {"FirstColumn", "SecondColumns"}, {"FirstColumn", "SecondColumns"}) in #"Expanded CreateNewTable"output is this
Copy paste this code to the advanced editor in a new blank query to see how the solution works.
If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too
Have fun
Jimmy
6 Replies
Hello brunofds
add a new column where you are generating your new table. Then delete all other columns and expand the new columns
here the formula
[FirstColumn = if Text.StartsWith([A],"Taxa de Administração do Agente Financeiro cobrada em") then [A] else [B], SecondColumns = if Text.StartsWith([A],"Taxa de Administração do Agente Financeiro cobrada em") then [B] else [C]]here the complete code
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("tVJNT8JAFPwrLz1BrNBSMHBsAE/4kcKNcHi0D7JJu9vsLsSfo/FA8O4v6B9ztyBRATWgt9d0dt68mRmPnRE+ICQEYZIxzpSWWKyKZwGJgHBOXBNcM448JiYFxGIqMUGgDLyg7jXqDa/hOa4TtH3X88FMfJGmzsT9K95aJ2i6QecTczm4TgUuoNqVxTphWthNXUvCixVatLcHvcc5ZmZxic1RxpSigkvzjBtxWiijIoOItJBcHKYIcyE1KRhSLEmjZEdW9Wgp0sX23iPLbu3PiCibilQVT0tiao9sGA56d9C/gcCve35pisX47WYz+IgblGeXxykoHmHnykF1EfHEiLHZ5CmLbS6v5qhdHFuh35r5WyfsLrZxPaIclUKTf06pgJHIzCz/18DTgvfbV63WiVX7GkWvWE8PJXEk2pO4znH6zDL83LyKeV81XxGpOeoyAATjO1czksULj011UEAv7PdB0qwGJi4talaR7dVsIy5+V7DhnbwB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [A = _t, B = _t, C = _t]), #"Added Custom" = Table.AddColumn(Source, "CreateNewTable", each [FirstColumn = if Text.StartsWith([A],"Taxa de Administração do Agente Financeiro cobrada em") then [A] else [B], SecondColumns = if Text.StartsWith([A],"Taxa de Administração do Agente Financeiro cobrada em") then [B] else [C]]), #"Removed Other Columns" = Table.SelectColumns(#"Added Custom",{"CreateNewTable"}), #"Expanded CreateNewTable" = Table.ExpandRecordColumn(#"Removed Other Columns", "CreateNewTable", {"FirstColumn", "SecondColumns"}, {"FirstColumn", "SecondColumns"}) in #"Expanded CreateNewTable"output is this
Copy paste this code to the advanced editor in a new blank query to see how the solution works.
If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too
Have fun
JimmyAfter some adjustments, that solution worked
Thank you 🙂
brunofds , you can manipulate the table like building blocks,
- truncate the lower part skipping the first 2 rows,
- adjust the order of columns for the first 2 rows
- combinate 1. and 2.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("tVJNT8JAFPwrLz1BrNBSMHBsAE/4kcKNcHi0D7JJu9vsLsSfo/FA8O4v6B9ztyBRATWgt9d0dt68mRmPnRE+ICQEYZIxzpSWWKyKZwGJgHBOXBNcM448JiYFxGIqMUGgDLyg7jXqDa/hOa4TtH3X88FMfJGmzsT9K95aJ2i6QecTczm4TgUuoNqVxTphWthNXUvCixVatLcHvcc5ZmZxic1RxpSigkvzjBtxWiijIoOItJBcHKYIcyE1KRhSLEmjZEdW9Wgp0sX23iPLbu3PiCibilQVT0tiao9sGA56d9C/gcCve35pisX47WYz+IgblGeXxykoHmHnykF1EfHEiLHZ5CmLbS6v5qhdHFuh35r5WyfsLrZxPaIclUKTf06pgJHIzCz/18DTgvfbV63WiVX7GkWvWE8PJXEk2pO4znH6zDL83LyKeV81XxGpOeoyAATjO1czksULj011UEAv7PdB0qwGJi4talaR7dVsIy5+V7DhnbwB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [A = _t, B = _t, C = _t]), LowerPart = Table.Skip(Source,2), UpperPart = Table.FirstN(Source,2), #"Reordered Columns" = Table.ReorderColumns(UpperPart,{"C", "A", "B"}), #"Renamed Columns" = Table.RenameColumns(#"Reordered Columns",{{"C", "A"},{"A", "B"},{"B", "C"}}), Combination = #"Renamed Columns" & LowerPart in CombinationDear CNENFRNL
Thanks for your time.
I'm afraid your solution won't deal the proper way as new tables are added in the data source
For instance, the formula
LowerPart = Table.Skip(Source,2)
have that number 2 "hardcoded", and will only work within THIS specific scenario i provided.... i'll be adding much more data, so it wont only 2 rows to skip. The same logic applies to
UpperPart = Table.FirstN(Source,2),
I need to have a dynamic way of computing this new tables been added.
I honestly appreciate your efforts, and found the solution quite clever, although it wont solve my issue, i've learned something new!
