Conditional - remove blanks and shift left in Power Query

[obiwilson]

Hi,

I have a large (40K) address database to clean up. The initial cleanup involves removing all the blank cells and shifting the data left (columns J to M), based upon what the address starts with (letters or numbers) it needs to be moved back to the either the Business Name column or the Address 1 column. And just to make things complicated, if the address begins with "shop" or "PO Box" it needs to be moved to the Address 1 column.

I have manually achieved this using Ctrl + G and removing blanks, shifting left, sorting a-z and moving Shop/PO Box 1 column across. However, I would like to automate the process up to this point for future cleaning but was unable to achieve the same results within Power Query. I tried some similar solution on the forums but was unable to get the unpivot/pivot solution or M code solutions to work.

Here is a sample of what I'm working with:

Associate ID	Associate Client Code	Prefix	First Name	Middle	Last Name	Suffix	Greeting	Specialty	Business Name	Address 1	Address 2	Address 3	City	State	Zip Code
70396157	I00:26:21		Test		Account		Doctor		Epping Plaza Medical & Dental Centre		Shop 216 Epping Plaza	571-583 High Street	EPPING	VIC	3076
91556921	I00:26:27		Test		Account		Doctor		Chelsea Arcade Medical		Shop 10 & 11	426 Nepean Highway	CHELSEA	VIC	3196
90094825	I00:26:37		Test		Account		Doctor			Bentleigh Medical Centre	254 Centre Road		BENTLEIGH	VIC	3204
57666989	I00:30:19		Test		Account		Doctor		Strathdale Medical Centre		5-7 Putnam Avenue		STRATHDALE	VIC	3550
80436127	I00:23:56		Test		Account		Doctor			Victorian Rehabilitation Centre		499 Springvale Road	GLEN WAVERLEY	VIC	3150
91437137	I00:32:45		Test		Account		Doctor		Bentleigh Medical Centre	254 Centre Road			BENTLEIGH	VIC	3204
309085570	I00:33:49		Test		Account		Doctor		Victoria Harbour Medical Centre		800 Bourke Street		DOCKLANDS	VIC	3008
63523884	I00:10:42		Test		Account		Doctor				Shop 49	Lonsdale Street	MELBOURNE	VIC	3000
103394111	I00:11:36		Test		Account		Doctor			Highlands Medical Centre	Shop 22, Stockland Highlands	300-332 Grand Boulevard	CRAIGIEBURN	VIC	3064
82192344	I00:36:28		Test		Account		Doctor				PO Box 123	379-381 Whitehorse Road	BALWYN	VIC	3103

And this is what I need it to be:

Associate ID	Associate Client Code	Prefix	First Name	Middle	Last Name	Suffix	Greeting	Specialty	Business Name	Address 1	Address 2	Address 3	City	State	Zip Code
70396157	I00:26:21		Test		Account		Doctor		Epping Plaza Medical & Dental Centre	Shop 216 Epping Plaza	571-583 High Street		EPPING	VIC	3076
91556921	I00:26:27		Test		Account		Doctor		Chelsea Arcade Medical	Shop 10 & 11	426 Nepean Highway		CHELSEA	VIC	3196
90094825	I00:26:37		Test		Account		Doctor		Bentleigh Medical Centre	254 Centre Road			BENTLEIGH	VIC	3204
57666989	I00:30:19		Test		Account		Doctor		Strathdale Medical Centre	5-7 Putnam Avenue			STRATHDALE	VIC	3550
80436127	I00:23:56		Test		Account		Doctor		Victorian Rehabilitation Centre	499 Springvale Road			GLEN WAVERLEY	VIC	3150
91437137	I00:32:45		Test		Account		Doctor		Bentleigh Medical Centre	254 Centre Road			BENTLEIGH	VIC	3204
309085570	I00:33:49		Test		Account		Doctor		Victoria Harbour Medical Centre	800 Bourke Street			DOCKLANDS	VIC	3008
63523884	I00:10:42		Test		Account		Doctor			Shop 49	1 Lonsdale Street		MELBOURNE	VIC	3000
103394111	I00:11:36		Test		Account		Doctor		Highlands Medical Centre	Shop 22, Stockland Highlands	300-332 Grand Boulevard		CRAIGIEBURN	VIC	3064
82192344	I00:36:28		Test		Account		Doctor			PO Box 123	379-381 Whitehorse Road		BALWYN	VIC	3103

Any help is greatly appreciated!

 

[JGordon11]

let
    Source = Table.Buffer(Excel.CurrentWorkbook(){[Name="Table1"]}[Content]),
    cols = List.Buffer({"Business Name","Address 1","Address 2","Address 3"}),
    tbl = Table.AddColumn(Source, "Custom", each 
        let 
            lst = List.RemoveNulls(Record.ToList(Record.SelectFields(_,cols))),
            lst1 =  if Text.Contains("0123456789", Text.Start(lst{0}, 1)) or 
                        Text.Lower(Text.Start(lst{0}, 5)) = "shop " or 
                        Text.Lower(Text.Start(lst{0}, 6)) = "po box"
                    then 
                        {null} & List.FirstN(lst,3)
                    else 
                        lst,
            lst2 = lst1 & List.Repeat({null}, 4 - List.Count(lst1))
        in Record.FromList(lst2, cols)),
    tbl1 = Table.RemoveColumns(tbl,cols),
    tbl2 = Table.ExpandRecordColumn(tbl1, "Custom", cols),
    Result = Table.ReorderColumns(tbl2,Table.ColumnNames(Source))
in
    Result

 

[obiwilson]

let
    Source = Table.Buffer(Excel.CurrentWorkbook(){[Name="Table1"]}[Content]),
    cols = List.Buffer({"Business Name","Address 1","Address 2","Address 3"}),
    tbl = Table.AddColumn(Source, "Custom", each
        let
            lst = List.RemoveNulls(Record.ToList(Record.SelectFields(_,cols))),
            lst1 =  if Text.Contains("0123456789", Text.Start(lst{0}, 1)) or
                        Text.Lower(Text.Start(lst{0}, 5)) = "shop " or
                        Text.Lower(Text.Start(lst{0}, 6)) = "po box"
                    then
                        {null} & List.FirstN(lst,3)
                    else
                        lst,
            lst2 = lst1 & List.Repeat({null}, 4 - List.Count(lst1))
        in Record.FromList(lst2, cols)),
    tbl1 = Table.RemoveColumns(tbl,cols),
    tbl2 = Table.ExpandRecordColumn(tbl1, "Custom", cols),
    Result = Table.ReorderColumns(tbl2,Table.ColumnNames(Source))
in
    Result

Thanks for your help.

When I run the above on my sample table it works 100% but when run on the actual DB there are many rows which contain blanks:

Hollie Drive Medical Centre	5 Hollie Drive			MORWELL	VIC	3840
St Kilda Road Medical Centre			1/391 St Kilda Road	MELBOURNE	VIC	3004
		259 St Georges Road		NORTHCOTE	VIC	3070
Lotus Medical Centre	Suite 3/200 Sydney Road			BRUNSWICK	VIC	3056
Peter MacCallum Cancer Centre	305 Grattan Street			MELBOURNE	VIC	3000
Heaths Road Family Clinic			241 Heaths Road	WERRIBEE	VIC	3030
Neurology Network Melbourne		Suite 3 Level 1	517 St Kilda Road	MELBOURNE	VIC	3004
Royal Victorian Eye and Ear Hospital			32 Gisborne Street	EAST MELBOURNE	VIC	3002
Masada Medical Centre	26 Balaclava Road			ST KILDA EAST	VIC	3183

It did delete and shift left some which makes me think these blank cells are not actually blank.

Any suggestion to strip out any hidden data prior to applying the above?

 

[JGordon11]

Copy one of the "blank" cells that isn't null and paste it into the Value To Find box of the Replace Value dialog box in the Power Query user interface. Type null in the Replace With box.

Once the "blank" cells show as null in PQ user interface then the code above should work

 

[obiwilson]

Copy one of the "blank" cells that isn't null and paste it into the Value To Find box of the Replace Value dialog box in the Power Query user interface. Type null in the Replace With box.

Once the "blank" cells show as null in PQ user interface then the code above should work

Thank you!
And may I say, your profile pic is spot on.