cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Frequent Visitor

Shift data left to null cell

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

6 REPLIES 6
Super User I
Super User I

@PBIhelp1 -

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"





Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

Proud to be a Super User!



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.

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

Hi,

Share some data and show the expected result.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

@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 #PartType<--Shift Left--Date<--Shift Left--Quantity Before
288Adult 3 Ply Face Masks (disposable)Update7/23/2020 8:27 0 97.95
288Adult 3 Ply Face Masks (disposable)Update7/23/2020 8:47 2156000 97.95
288Adult 3 Ply Face Masks (disposable)Update8/3/2020 16:21 2156000 0
288Adult 3 Ply Face Masks (disposable)Update8/3/2020 16:24 3565650 0

After >>>

Part #PartTypeDateQuantity BeforeQuantity After
288Adult 3 Ply Face Masks (disposable)Update7/23/2020 8:27097.95
288Adult 3 Ply Face Masks (disposable)Update7/23/2020 8:47215600097.95
288Adult 3 Ply Face Masks (disposable)Update8/3/2020 16:2121560000
288Adult 3 Ply Face Masks (disposable)Update8/3/2020 16:2435656500

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.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Helpful resources

Announcements
secondImage

Happy New Year from Power BI

This is a must watch for a message from Power BI!

December Update

Check it Out!

Click here to read more about the December 2020 Updates!

Community Blog

Check it Out!

Click here to read the latest blog and learn more about contributing to the Power BI blog!

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors
Top Kudoed Authors