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)?

 

ColumnAColumnB
1

a

 

2c
3d
nulls
5a
nullf
1f
2r
nullu

 

The break is null.

 

Thanks

 

S

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

4 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Thank you 🙂

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    4 years ago

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

  • 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