Shift data left to null cell

[Anonymous]

I'm wondering if it is possible to replicate the Excel trick where you select all empty cells using crtl-g, delete values and shift cells left. I'm exporting and merging data from 100s of pdfs and even though the columns line up in the PDFs, for whatever reason, in the output in Power Query, some of the PDFs have columns with null values. It seems like it would be easy to fix if I could just shift data left to null cells so that all the data lines up. Is there a way to replicate the sift cells left functionality in Power Query, or perhaps another approach to solve this issue?

Before -

Col 1  Col 2  Col 3  Col 4

 1        null       1        1

 1         1         1      null

 1         1         1      null

After -

Col 1  Col 2  Col 3  Col 4

 1         1        1      null

 1         1         1      null

 1         1         1      null

Thanks

[jackieb727]

For anyone out there looking for a way to do this with a few simple clicks, I was able to get all data shifted left by the following:

In Power Query;

1. Highlight all columns that you want the data shifted left.
2. Select 'Merge Columns'.  Use equals sign as the separator (don't use semi-colon, didn't work this way). Select OK.
3. Then right click on your new merged column, and select 'split column'. 
   1. Split by the equals sign delimeter
   2. Choose 'split into columns' (under Advanced options). 
   3. Enter how many columns to split into (probably the same number of columns as original in case there's a row(s) where data exists in each one)
   4. Click OK.  
4. All of your values are now shifted left in the columns.

[ChrisMendoza]

@Anonymous -

Possibly merge the columns (2,3,4), delimit by space, then trim, then split column by space?

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUQIiQzCO1YmGsiAYi0AsAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t, Column4 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", Int64.Type}, {"Column2", Int64.Type}, {"Column3", Int64.Type}, {"Column4", Int64.Type}}),
    #"Merged Columns" = Table.CombineColumns(Table.TransformColumnTypes(#"Changed Type", {{"Column2", type text}, {"Column3", type text}, {"Column4", type text}}, "en-US"),{"Column2", "Column3", "Column4"},Combiner.CombineTextByDelimiter(" ", QuoteStyle.None),"Merged"),
    #"Trimmed Text" = Table.TransformColumns(#"Merged Columns",{{"Merged", Text.Trim, type text}}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Trimmed Text", "Merged", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), {"Merged.1", "Merged.2"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Merged.1", Int64.Type}, {"Merged.2", Int64.Type}})
in
    #"Changed Type1"

[Anonymous]

That works when the null spaces are at the start or end, but I'm actually trying to apply this solution to many columns at once ( I should have mentioned this) and the TRIM function does not seem to work for extra spaces between text.

[lso4jw]

@Anonymous: I would like to know how to shift cells too. Did you ever come up with a solution?

[Ashish_Mathur]

Hi,

Share some data and show the expected result.

[lso4jw]

@Ashish_Mathur: I need to pull this data weekly for reports, but the system shifts the column name when exporting it to a CSV file. Thank you for any tips you can give.

Before >>>

Part #	Part	Type	<--Shift Left--	Date	<--Shift Left--	Quantity Before
288	Adult 3 Ply Face Masks (disposable)	Update	7/23/2020 8:27		0		97.95
288	Adult 3 Ply Face Masks (disposable)	Update	7/23/2020 8:47		2156000		97.95
288	Adult 3 Ply Face Masks (disposable)	Update	8/3/2020 16:21		2156000		0
288	Adult 3 Ply Face Masks (disposable)	Update	8/3/2020 16:24		3565650		0

After >>>

Part #	Part	Type	Date	Quantity Before	Quantity After
288	Adult 3 Ply Face Masks (disposable)	Update	7/23/2020 8:27	0	97.95
288	Adult 3 Ply Face Masks (disposable)	Update	7/23/2020 8:47	2156000	97.95
288	Adult 3 Ply Face Masks (disposable)	Update	8/3/2020 16:21	2156000	0
288	Adult 3 Ply Face Masks (disposable)	Update	8/3/2020 16:24	3565650	0

[Ashish_Mathur]

Hi,

Simply delete the columns with the heading of Date and Quantity before and rename the columns with no headings to Date and Quantity before.