# Power Query to Concatenate Rows With null Values



## barberic5 (Nov 23, 2021)

Hello, I'm new to using PowerQuery and hoping to get some help with an issue I ran into. when I import data, I need to concatenate all the rows in column A and column B where the text spills over into the next row. The number of rows the values spill over varies based on the text length of the Item and the Description. I need to remove all the rows with null values and concatenate the values in column A and column B from the deleted rows with null values. So in the end there should be six rows including the headers in this example.


----------



## alansidman (Nov 24, 2021)

Cannot manipulate data in a picture.  Suggest you reload your data using XL2BB.  If you are unfamiliar with XL2BB look in my signature for instructions.


----------



## barberic5 (Nov 24, 2021)

alansidman said:


> Cannot manipulate data in a picture.  Suggest you reload your data using XL2BB.  If you are unfamiliar with XL2BB look in my signature for instructions.


Sure thing, here it is:

Power Query Test Example.xlsxABCDE1ITEMDESCRIPTIONQTYRATEAMOUNT2MiniNail Kit BlackMiniNail Micro Enail Kit with Black Controller, Hybrid2$1,000.00$2,000.003with RainbowCoil, Rainbow Quartz/Titanium Hybrid Nail, Rainbownullnullnull4Hybrid Nail andFlat Tip Carb Cap and Dabber, Slab Pad and Powernullnullnull5Rainbow Flat Tip
DabberCordnullnullnull6MiniNail KitMiniNail Micro Enail Kit with Purple Controller,1$1,000.00$1,000.007Purple withHybrid Coil, Rainbow Quartz/Titanium Hybrid Nail,nullnullnull8Rainbow HybridRainbow Flat Tip Carb Cap and Dabber, Slab Padnullnullnull9Nail and Rainbow
Flat Tip Dabberand Power Cordnullnullnull10MiniNail Kit SilverMiniNail Micro Enail Kit with Silver Controller, Hybrid1$1,000.00$1,000.0011with Silver HybridCoil, Silver Quartz/Titanium Hybrid Nail, Silver Ninjanullnullnull12Nail and SilverSword Carb Cap and Dabber, Slab Pad and Powernullnullnull13Ninja Sword
DabberCordnullnullnull1425mm Quartz25mm Quartz Banger with 90 Degree Bend and1$500.00$500.0015Banger14mm Male Jointnullnullnull16Replacement
Dish Pack -Quartz Deep DishReplacement Dish Pack - Quartz Deep Dish1$500.00$500.00Table002 (Page 1)


----------



## RoryA (Nov 24, 2021)

I'm sure there are more direct ways, but this should do the job:


```
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Added Index" = Table.AddIndexColumn(Source, "Index", 0, 1),
    #"Added Custom" = Table.AddColumn(#"Added Index", "Grouper", each if [QTY] is null then null else [Index]),
    #"Filled Down" = Table.FillDown(#"Added Custom",{"Grouper", "QTY", "RATE", "AMOUNT"}),
    #"Removed Columns" = Table.RemoveColumns(#"Filled Down",{"Index"}),
    #"Grouped Rows" = Table.Group(#"Removed Columns", {"QTY", "RATE", "AMOUNT", "Grouper"}, {{"Items", each Text.Combine([ITEM], " "), type text}, {"Descriptions", each Text.Combine([DESCRIPTION], " "), type text}})
in
    #"Grouped Rows"
```


----------



## barberic5 (Dec 3, 2021)

That did the trick, thanks so much! Is there a way to go about this with the UI instead of code?

Also, sorry for the late response. I had fallen ill and then had some work to catch up on.


----------



## RoryA (Dec 4, 2021)

All those steps were done via the UI.


----------

