Power Query to Concatenate Rows With null Values

[barberic5]

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]

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]

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.xlsx
	A	B	C	D	E
1	ITEM	DESCRIPTION	QTY	RATE	AMOUNT
2	MiniNail Kit Black	MiniNail Micro Enail Kit with Black Controller, Hybrid	2	$1,000.00	$2,000.00
3	with Rainbow	Coil, Rainbow Quartz/Titanium Hybrid Nail, Rainbow	null	null	null
4	Hybrid Nail and	Flat Tip Carb Cap and Dabber, Slab Pad and Power	null	null	null
5	Rainbow Flat Tip Dabber	Cord	null	null	null
6	MiniNail Kit	MiniNail Micro Enail Kit with Purple Controller,	1	$1,000.00	$1,000.00
7	Purple with	Hybrid Coil, Rainbow Quartz/Titanium Hybrid Nail,	null	null	null
8	Rainbow Hybrid	Rainbow Flat Tip Carb Cap and Dabber, Slab Pad	null	null	null
9	Nail and Rainbow Flat Tip Dabber	and Power Cord	null	null	null
10	MiniNail Kit Silver	MiniNail Micro Enail Kit with Silver Controller, Hybrid	1	$1,000.00	$1,000.00
11	with Silver Hybrid	Coil, Silver Quartz/Titanium Hybrid Nail, Silver Ninja	null	null	null
12	Nail and Silver	Sword Carb Cap and Dabber, Slab Pad and Power	null	null	null
13	Ninja Sword Dabber	Cord	null	null	null
14	25mm Quartz	25mm Quartz Banger with 90 Degree Bend and	1	$500.00	$500.00
15	Banger	14mm Male Joint	null	null	null
16	Replacement Dish Pack -Quartz Deep Dish	Replacement Dish Pack - Quartz Deep Dish	1	$500.00	$500.00
Table002 (Page 1)

 

[RoryA]

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]

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]

All those steps were done via the UI.