Power Query - Eliminate blank cells in each column (generate lists for nonblank data)

[procha]

Hello,

I searched a lot but didn't find an answer to this.

I have loaded in power query 4 columns like this (after some tweaking)

REP 1	REP 2	REP 3	REP 4
null	Customer1	null	null
Customer2	null	null	null
Customer3	null	null	null
null	null	Customer4	null
null	null	null	Customer5

I'd like to have a list like this:

REP 1	REP 2	REP 3	REP4
Customer2	Customer1	Customer4	Customer5
Customer3	null	null	null

Any help appreciated.

Thank you

 

[JGordon11]

let
    Source = Excel.CurrentWorkbook(){[Name="Table3"]}[Content],
    lst = Table.ToColumns(Source),
    lst1 = List.Transform(lst, each List.RemoveNulls(_)),
    Result = Table.FromColumns(lst1, Table.ColumnNames(Source))
in
    Result

Book1
	A	B	C	D	E	F	G	H	I	J
1	REP 1	REP 2	REP 3	REP 4		REP 1	REP 2	REP 3	REP 4
2		Customer1				Customer2	Customer1	Customer4	Customer5
3	Customer2					Customer3
4	Customer3
5			Customer4
6				Customer5
7
Sheet3

 

[lrobbo314]

Nice technique @JGordon11.

I went a roundabout way of doing it. Always forget about Table.ToColumns.

let
    Source = Excel.CurrentWorkbook(){[Name="Table7"]}[Content],
    Transpose = Table.Transpose(Source),
    TR = Table.TransformRows(Transpose,(row)=> List.RemoveNulls(Record.ToList(row))),
    ToTable = Table.FromList(TR, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    Extract = Table.TransformColumns(ToTable, {"Column1", each Text.Combine(List.Transform(_, Text.From), ","), type text}),
    Split = Table.SplitColumn(Extract, "Column1", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"Column1.1", "Column1.2"}),
    Transpose1 = Table.Transpose(Split)
in
    Transpose1

 

[procha]

let
    Source = Excel.CurrentWorkbook(){[Name="Table3"]}[Content],
    lst = Table.ToColumns(Source),
    lst1 = List.Transform(lst, each List.RemoveNulls(_)),
    Result = Table.FromColumns(lst1, Table.ColumnNames(Source))
in
    Result

Beautiful, thank you so much!

I could never get there using the predefined actions.

 

[procha]

Nice technique @JGordon11.

I went a roundabout way of doing it. Always forget about Table.ToColumns.

let
    Source = Excel.CurrentWorkbook(){[Name="Table7"]}[Content],
    Transpose = Table.Transpose(Source),
    TR = Table.TransformRows(Transpose,(row)=> List.RemoveNulls(Record.ToList(row))),
    ToTable = Table.FromList(TR, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    Extract = Table.TransformColumns(ToTable, {"Column1", each Text.Combine(List.Transform(_, Text.From), ","), type text}),
    Split = Table.SplitColumn(Extract, "Column1", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"Column1.1", "Column1.2"}),
    Transpose1 = Table.Transpose(Split)
in
    Transpose1

Thank you also, but I did JGordon11 way

 

[lrobbo314]

Thank you also, but I did JGordon11 way

For sure. I will from now on as well.