# Delete Blanks and shift cells to left in Power Query



## User81 (Apr 17, 2021)

Hi there, 
i am having the following issue. I need to tidy up my data table because i get data in different columns. As an example :

CodeDescrColumn1PriceColumn2Column3Column411​Shirt5​15​Jeans5​17​Trousers5​
while the table should be :


CodeDescrPrice11​Shirt5​15​Jeans5​17​Trousers5​

in excel it was quite an easy going process (Choose blank cells, then delete and shift cells to left ), is there a way to automate this process with power querry?


----------



## alansidman (Apr 17, 2021)

Unpivot your data and remove the extra columns


```
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Code", type text}, {"Descr", type text}, {"Column1", type text}, {"Price", type any}, {"Column2", type text}, {"Column3", type any}, {"Column4", Int64.Type}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Code", "Descr"}, "Attribute", "Value"),
    #"Removed Columns" = Table.RemoveColumns(#"Unpivoted Other Columns",{"Attribute"})
in
    #"Removed Columns"
```


----------



## User81 (Apr 17, 2021)

alansidman said:


> let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content], #"Changed Type" = Table.TransformColumnTypes(Source,{{"Code", type text}, {"Descr", type text}, {"Column1", type text}, {"Price", type any}, {"Column2", type text}, {"Column3", type any}, {"Column4", Int64.Type}}), #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Code", "Descr"}, "Attribute", "Value"), #"Removed Columns" = Table.RemoveColumns(#"Unpivoted Other Columns",{"Attribute"}) in #"Removed Columns"


alansidman thank you very much, 

works like a charm!!


----------

