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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
renatopnovaes
Helper I
Helper I

Concatenate two consecutive rows

I extract a txt report from the company's accounts, but the report comes out with two lines. By power query I can transform this report into a table, but I wanted to contact the information below in the Observation column.

 

Example TxT extracted from system: 

SICOOB CRE - DINHEIRO - RECEITA FEDERAL 200721 20/07/21 273,45-
                       OBSERVAÇÃO - *P /INSS CONTAGEM JUN/21 200721 20/07/21
SICOOB CRE - DINHEIRO - RECEITA FEDERAL 200721 20/07/21 1.052,90-
                        OBSERVAÇÃO - *N /INSS BELO HORIZONTE JUN/21 200721 20/07/21
SICOOB CRE - DINHEIRO - RECEITA FEDERAL 200721 20/07/21 1.338,95-
                       OBSERVAÇÃO - *G /INSS BELO HORIZONTE JUN/21 200721 20/07/21

 

after treatment in the power query the table looks like this:

ACCOUNTSPECIESDESCRIPTIONCODE DOCUMENTDATEVALUE
SICOOB CREDINHEIRORECEITA FEDERAL20072120/07/21273,45-
 OBSERVAÇÃO*P /INSS CONTAGEM JUN/2120072120/07/21 
SICOOB CREDINHEIRORECEITA FEDERAL20072120/07/211.052,90-
 OBSERVAÇÃO*N /INSS BELO HORIZONTE JUN/2120072120/07/21 
SICOOB CRE DINHEIRORECEITA FEDERAL20072120/07/211.338,95-
 OBSERVAÇÃO*G /INSS BELO HORIZONTE200721 20/07/21 

 

But my target is this:

ACCOUNTSPECIESDESCRIPTIONCODE DOCUMENTDATEVALUE
SICOOB CREDINHEIRORECEITA FEDERAL +  *P /INSS CONTAGEM JUN/2120072120/07/21273,45-
SICOOB CREDINHEIRORECEITA FEDERAL *N /INSS BELO HORIZONTE JUN/2120072120/07/211.052,90-
SICOOB CREDINHEIRORECEITA FEDERAL + *G /INSS BELO HORIZONTE20072120/07/211.338,95-

My idea is to concatenate only the description column information and ignore the other information. Remembering that the word "OBSERVAÇÃO" is in all instances of an account having two lines.

2 ACCEPTED SOLUTIONS
edhans
Super User
Super User

You can try this quick pattern @renatopnovaes 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("pdBBDoIwEAXQq0y6NFVKkSDLUkaowY5p0YWE07jwYFzMiiw0QRN192eS+XmZrmPeaKICtEPGWWlsjcZRiA41mlbBFkt0qgkbKUQm4zFEIoseMUv4Ol2ynncMwkyFR3dSw3W43EsWB4iM9R402VZVuIfd0U6XM20w9vwnilcilTwX7012MhXYENTkzDng8GsZ/EBLkg3PP7yrmqU918Erqr8B", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ACCOUNT = _t, SPECIES = _t, DESCRIPTION = _t, #"CODE DOCUMENT" = _t, DATE = _t, VALUE = _t]),
    #"Added Custom" = Table.AddColumn(Source, "Description 2", each if [ACCOUNT] = " " then [DESCRIPTION] else null),
    #"Filled Up" = Table.FillUp(#"Added Custom",{"Description 2"}),
    #"Merged Columns" = Table.CombineColumns(#"Filled Up",{"DESCRIPTION", "Description 2"},Combiner.CombineTextByDelimiter(",", QuoteStyle.None),"Description"),
    #"Filtered Rows" = Table.SelectRows(#"Merged Columns", each ([ACCOUNT] = "SICOOB CRE")),
    #"Reordered Columns" = Table.ReorderColumns(#"Filtered Rows",{"ACCOUNT", "SPECIES", "CODE DOCUMENT", "Description", "DATE", "VALUE"})
in
    #"Reordered Columns"

 

It returns this.

edhans_0-1627584492061.png

Here is what it does:

  1. Adds a new column. If [Account] = " " (a space) then pull the Description 2 coluimn, otherwise return null. You may need to change the space to a blank "" or null depending on your real data. Pasting into the forum can mess up blanks and nulls.
  2. In the new column, right-click and Fill Up.
  3. Filter the Account column to remove the blank/null/whatever.
  4. Merge the two Description columns by clicking on both, right-click then Merge. Pick a delimiter fi you want.
  5. Get rid of whatever else you want.

How to use M code provided in a blank query:
1) In Power Query, select New Source, then Blank Query
2) On the Home ribbon, select "Advanced Editor" button
3) Remove everything you see, then paste the M code I've given you in that box.
4) Press Done
5) See this article if you need help using this M code in your model.

 



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

View solution in original post

Greg_Deckler
Super User
Super User

@renatopnovaes How about:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("pdBBDoIwEAXQq0y6NFVKkSDLUkaowY5p0YWE07jwYFzMiiw0QRN192eS+XmZrmPeaKICtEPGWWlsjcZRiA41mlbBFkt0qgkbKUQm4zFEIoseMUv4Ol2ynncMwkyFR3dSw3W43EsWB4iM9R402VZVuIfd0U6XM20w9vwnilcilTwX7012MhXYENTkzDng8GsZ/EBLkg3PP7yrmqU918Erqr8B", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ACCOUNT = _t, SPECIES = _t, DESCRIPTION = _t, #"CODE DOCUMENT" = _t, DATE = _t, VALUE = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ACCOUNT", type text}, {"SPECIES", type text}, {"DESCRIPTION", type text}, {"CODE DOCUMENT", Int64.Type}, {"DATE", type text}, {"VALUE", type text}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 1, 1, Int64.Type),
    #"Added Custom" = Table.AddColumn(#"Added Index", "Custom", each Number.RoundUp([Index]/2)),
    Odds = Table.SelectRows(#"Added Custom", each Number.IsOdd([Index])),
    Evens = Table.SelectRows(#"Added Custom", each Number.IsEven([Index])),
    Merged = Table.NestedJoin(Odds,{"Custom"},Evens,{"Custom"},"NewColumn",JoinKind.LeftOuter),
    #"Expanded NewColumn" = Table.ExpandTableColumn(Merged, "NewColumn", {"DESCRIPTION"}, {"NewColumn.DESCRIPTION"}),
    #"Merged Columns" = Table.CombineColumns(#"Expanded NewColumn",{"DESCRIPTION", "NewColumn.DESCRIPTION"},Combiner.CombineTextByDelimiter("", QuoteStyle.None),"Merged"),
    #"Removed Columns" = Table.RemoveColumns(#"Merged Columns",{"Index", "Custom"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Merged", "DESCRIPTION"}})
in
    #"Renamed Columns"

@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

6 REPLIES 6
Greg_Deckler
Super User
Super User

@renatopnovaes How about:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("pdBBDoIwEAXQq0y6NFVKkSDLUkaowY5p0YWE07jwYFzMiiw0QRN192eS+XmZrmPeaKICtEPGWWlsjcZRiA41mlbBFkt0qgkbKUQm4zFEIoseMUv4Ol2ynncMwkyFR3dSw3W43EsWB4iM9R402VZVuIfd0U6XM20w9vwnilcilTwX7012MhXYENTkzDng8GsZ/EBLkg3PP7yrmqU918Erqr8B", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ACCOUNT = _t, SPECIES = _t, DESCRIPTION = _t, #"CODE DOCUMENT" = _t, DATE = _t, VALUE = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ACCOUNT", type text}, {"SPECIES", type text}, {"DESCRIPTION", type text}, {"CODE DOCUMENT", Int64.Type}, {"DATE", type text}, {"VALUE", type text}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 1, 1, Int64.Type),
    #"Added Custom" = Table.AddColumn(#"Added Index", "Custom", each Number.RoundUp([Index]/2)),
    Odds = Table.SelectRows(#"Added Custom", each Number.IsOdd([Index])),
    Evens = Table.SelectRows(#"Added Custom", each Number.IsEven([Index])),
    Merged = Table.NestedJoin(Odds,{"Custom"},Evens,{"Custom"},"NewColumn",JoinKind.LeftOuter),
    #"Expanded NewColumn" = Table.ExpandTableColumn(Merged, "NewColumn", {"DESCRIPTION"}, {"NewColumn.DESCRIPTION"}),
    #"Merged Columns" = Table.CombineColumns(#"Expanded NewColumn",{"DESCRIPTION", "NewColumn.DESCRIPTION"},Combiner.CombineTextByDelimiter("", QuoteStyle.None),"Merged"),
    #"Removed Columns" = Table.RemoveColumns(#"Merged Columns",{"Index", "Custom"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Merged", "DESCRIPTION"}})
in
    #"Renamed Columns"

@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
edhans
Super User
Super User

You can try this quick pattern @renatopnovaes 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("pdBBDoIwEAXQq0y6NFVKkSDLUkaowY5p0YWE07jwYFzMiiw0QRN192eS+XmZrmPeaKICtEPGWWlsjcZRiA41mlbBFkt0qgkbKUQm4zFEIoseMUv4Ol2ynncMwkyFR3dSw3W43EsWB4iM9R402VZVuIfd0U6XM20w9vwnilcilTwX7012MhXYENTkzDng8GsZ/EBLkg3PP7yrmqU918Erqr8B", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ACCOUNT = _t, SPECIES = _t, DESCRIPTION = _t, #"CODE DOCUMENT" = _t, DATE = _t, VALUE = _t]),
    #"Added Custom" = Table.AddColumn(Source, "Description 2", each if [ACCOUNT] = " " then [DESCRIPTION] else null),
    #"Filled Up" = Table.FillUp(#"Added Custom",{"Description 2"}),
    #"Merged Columns" = Table.CombineColumns(#"Filled Up",{"DESCRIPTION", "Description 2"},Combiner.CombineTextByDelimiter(",", QuoteStyle.None),"Description"),
    #"Filtered Rows" = Table.SelectRows(#"Merged Columns", each ([ACCOUNT] = "SICOOB CRE")),
    #"Reordered Columns" = Table.ReorderColumns(#"Filtered Rows",{"ACCOUNT", "SPECIES", "CODE DOCUMENT", "Description", "DATE", "VALUE"})
in
    #"Reordered Columns"

 

It returns this.

edhans_0-1627584492061.png

Here is what it does:

  1. Adds a new column. If [Account] = " " (a space) then pull the Description 2 coluimn, otherwise return null. You may need to change the space to a blank "" or null depending on your real data. Pasting into the forum can mess up blanks and nulls.
  2. In the new column, right-click and Fill Up.
  3. Filter the Account column to remove the blank/null/whatever.
  4. Merge the two Description columns by clicking on both, right-click then Merge. Pick a delimiter fi you want.
  5. Get rid of whatever else you want.

How to use M code provided in a blank query:
1) In Power Query, select New Source, then Blank Query
2) On the Home ribbon, select "Advanced Editor" button
3) Remove everything you see, then paste the M code I've given you in that box.
4) Press Done
5) See this article if you need help using this M code in your model.

 



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

@edhans quicker


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Depends on the dataset size. Merging a table against itself @Greg_Deckler can be super slow with thousands of records. There is a better but more complex way to do that if necessary with a List added offset by 1 as a new column. But for this, thie Fill Up seemed to work well give it was just one field that was needed, and I don't know of any performance issues on Fill Up over thousands of records.



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

@edhans Oh, I was just saying that you were quicker than me to get your answer in!! 🙂 But, yes, you are correct in your reply as well!


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

🤣🤣🤣

Ahh... my misunderstanding. 



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors