Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago

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

Need Help

7 Replies

  • ChrisMendoza's avatar
    ChrisMendoza
    Resident Rockstar
    7 years ago

    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's avatar
      Anonymous
      Not applicable
      7 years ago

      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's avatar
        lso4jw
        Frequent Visitor
        5 years ago

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    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.