Delete Blanks and shift cells to left in Power Query

[User81]

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 :

Code	Descr	Column1	Price	Column2	Column3	Column4
11​	Shirt	5​
15​	Jeans			5​
17​	Trousers					5​

while the table should be :

Code	Descr	Price
11​	Shirt	5​
15​	Jeans	5​
17​	Trousers	5​

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]

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]

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!!