cancel
Showing results for 
Search instead for 
Did you mean: 
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!!!
Check out my External Tool for Power BI Desktop!
Microsoft Hates Greg's Quick Measures
Check out my latest book!

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!!!
Check out my External Tool for Power BI Desktop!
Microsoft Hates Greg's Quick Measures
Check out my latest book!

View solution in original post

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

@edhans quicker


@ me in replies or I'll lose your thread!!!
Check out my External Tool for Power BI Desktop!
Microsoft Hates Greg's Quick Measures
Check out my latest book!

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!!!
Check out my External Tool for Power BI Desktop!
Microsoft Hates Greg's Quick Measures
Check out my latest book!

🤣🤣🤣

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
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Power BI October Update 2021.jpg

Power BI Release

Click here to read more about the October 2021 Release!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Teds Dev Camp Oct. 2021 768x460.jpg

Power BI Dev Camp - October 28th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors
Top Kudoed Authors