Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Removing Blocks - Data Preparation

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 nu...
Need Help
  • Greg_Deckler's avatar
    Greg_Deckler
    4 years ago

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

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's avatar
Greg_Deckler
Community Champion
4 years ago

Anonymous 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