Forum Discussion
Concatenate two consecutive rows
- 5 years ago
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.
Here is what it does:
- 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.
- In the new column, right-click and Fill Up.
- Filter the Account column to remove the blank/null/whatever.
- Merge the two Description columns by clicking on both, right-click then Merge. Pick a delimiter fi you want.
- 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. - 5 years ago
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"
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.
Here is what it does:
- 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.
- In the new column, right-click and Fill Up.
- Filter the Account column to remove the blank/null/whatever.
- Merge the two Description columns by clicking on both, right-click then Merge. Pick a delimiter fi you want.
- 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.
- Greg_Deckler5 years agoCommunity Champion
edhans quicker
- edhans5 years agoCommunity Champion
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.
- Greg_Deckler5 years agoCommunity Champion
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!