Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
brunofds
Helper I
Helper I

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:

 

ABC
Taxa de Administração do Agente Financeiro cobrada em 03/02/2020381,01 null
Taxa de Administração do Agente Financeiro cobrada em 03/02/20203.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
nullSALDO EM 31/01/2020 18443
nullLanç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
nullLançamentos à Débito 0
nullSALDO EM 31/01/2020 0
nullLançamentos à Débito 0
null( + )Rendimento Repassado pelo Tomador 0
null( + )Rendas de Aplicações Financeiras - Cobrança 0
nullLançamentos à Crédito 0
( - )Resgate para transferência ao DAEE ref. Pagto. de tarifas de cobrança0

 

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) :

 

 

ABC
nullTaxa de Administração do Agente Financeiro cobrada em 03/02/2020381,01 
nullTaxa de Administração do Agente Financeiro cobrada em 03/02/20203.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
nullSALDO EM 31/01/2020 18443
nullLanç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
nullLançamentos à Débito 0
nullSALDO EM 31/01/2020 0
nullLançamentos à Débito 0
null( + )Rendimento Repassado pelo Tomador 0
null( + )Rendas de Aplicações Financeiras - Cobrança 0
nullLançamentos à Crédito 0
( - )Resgate para transferência ao DAEE ref. Pagto. de tarifas de cobrança0

 

 

Thank you.

 
 
 
 
 
1 ACCEPTED SOLUTION
Jimmy801
Community Champion
Community Champion

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

Jimmy801_0-1615547852639.png

 

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

View solution in original post

6 REPLIES 6
Jimmy801
Community Champion
Community Champion

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

Jimmy801_0-1615547852639.png

 

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

After some adjustments, that solution worked

 

Thank you 🙂

CNENFRNL
Community Champion
Community Champion

@brunofds , you can manipulate the table like building blocks,

  1. truncate the lower part skipping the first 2 rows,
  2. adjust the order of columns for the first 2 rows
  3. 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
    Combination

 


Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension!

DAX is simple, but NOT EASY!

Dear  @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!

@brunofds , of coz you need to tweak the code according to the scenario,

LowerPart = Table.Skip(Source, each [A] <> "null"),
UpperPart = Table.FirstN(Source, each [A] <> "null")

Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension!

DAX is simple, but NOT EASY!

@CNENFRNL  Actually i was reading the official documentation after you provided this solution. The null sollution you provided still will be incomplete 

 

I was thinking of:

 

Lower part should be something as:

LowerPart = Table.Skip(Source, each [A] Text.Contains( "Taxa de Administração do Agente Financeiro cobrada")

 

and UpperPart the opposite:

 

LowerPart = Table.Skip(Source, each [A] not Text.Contains( "Taxa de Administração do Agente Financeiro cobrada em 03/02/2020")

 

 

But i cannont insert those kind of text.functions that way.

 

Any ideas?

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors
Top Kudoed Authors