Removing Blocks - Data Preparation

[Stuartm1983]

Hi,

I'm looking to prepare my data with repeating blocks. Could anyone please help me as to how to remove the first block only (1-null)?

ColumnA	ColumnB
1	a
2	c
3	d
null	s
5	a
null	f
1	f
2	r
null	u

The break is null.

Thanks

S

[Stuartm1983]

Thank you 🙂

[Stuartm1983]

thanks for answering but I think you're misunderstanding my problem. I have not been too clear.

Basically I want to remove rows 1,2,3,null at the top each refresh. But these rows change in size but there is always a break of null.

[Greg_Deckler · Accepted solution]

@Stuartm1983 Ah, try this:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUUpUitWJVjICspLBLGMgKwXMyivNyQFyisEcU7hSqHAamGMIZ4FMKEJWUKoUGwsA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ColumnA = _t, ColumnB = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ColumnA", Int64.Type}, {"ColumnB", type text}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1, Int64.Type),
    #"Sorted Rows" = Table.Sort(#"Added Index",{{"Index", Order.Ascending}}),
    FirstNull = List.PositionOf(Table.Column(#"Sorted Rows","ColumnA"), null),
    FinalTable = Table.SelectRows(#"Sorted Rows",each [Index]>FirstNull)
in
    FinalTable

[Greg_Deckler]

@Stuartm1983 Try Trim operation in Power Query? Or Trim and Clean just to be safe?