Forum Discussion

clo_64's avatar
clo_64
Frequent Visitor
3 years ago
Solved

How can I split an excel sheet containing 3 tables in Power Query (Power BI)

Hi,

 

I'm trying to find a way to split one excel sheet which contains 3 tables with dynamic sizes in 3 separate tables to work independently on them in Power BI.

The spreadsheet looks like:

Current Consumers

Cust A       234

Cust B       115

Cust C       97

...

 

New Consumers
Cust D      5

...

Canceled Consumers
Cust E       7
Cust D      12
....
-------------------------------

There is a blanck row between each table.


And I would like to get 3 distinct tables out of this one sheet.
Any idea on how to do that?

Need Help
  • v-jingzhang's avatar
    v-jingzhang
    3 years ago

    Hi clo_64 

     

    I have create a sample file attached at bottom. There are three queries in the sample. Their only difference is the last step to filter rows. 

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wci4tKkrNK1Fwzs8rLs1NLSpWitUBiRaXKDgeWqAAQ0bGJggJJyQJQ0NThIQzkoSlOVgcTPillmOzwAWu3BSh1jkxLzk1JzUFmwZXJPPNsZljaKQUGwsA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    #"Added Index" = Table.AddIndexColumn(Source, "Index", 0, 1, Int64.Type),
    #"Added Custom" = Table.AddColumn(#"Added Index", "Custom", each if [Column1]="" then [Index] else null),
    /* The following three steps are all based on the result of "Added Custom" step */
    firstBlankRow = List.Min(Table.Column(#"Added Custom", "Custom")),
    secondBlankRow = List.Max(Table.Column(#"Added Custom", "Custom")),
    #"Filtered Rows" = Table.SelectRows(#"Added Custom", each [Index] < firstBlankRow)
in
    #"Filtered Rows"

     

    Best Regards,
    Community Support Team _ Jing
    If this post helps, please Accept it as Solution to help other members find it. Highly appreciate your Kudos!

2 Replies

  • v-jingzhang's avatar
    v-jingzhang
    Community Support
    3 years ago

    Hi clo_64 

     

    I have create a sample file attached at bottom. There are three queries in the sample. Their only difference is the last step to filter rows. 

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wci4tKkrNK1Fwzs8rLs1NLSpWitUBiRaXKDgeWqAAQ0bGJggJJyQJQ0NThIQzkoSlOVgcTPillmOzwAWu3BSh1jkxLzk1JzUFmwZXJPPNsZljaKQUGwsA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    #"Added Index" = Table.AddIndexColumn(Source, "Index", 0, 1, Int64.Type),
    #"Added Custom" = Table.AddColumn(#"Added Index", "Custom", each if [Column1]="" then [Index] else null),
    /* The following three steps are all based on the result of "Added Custom" step */
    firstBlankRow = List.Min(Table.Column(#"Added Custom", "Custom")),
    secondBlankRow = List.Max(Table.Column(#"Added Custom", "Custom")),
    #"Filtered Rows" = Table.SelectRows(#"Added Custom", each [Index] < firstBlankRow)
in
    #"Filtered Rows"

     

    Best Regards,
    Community Support Team _ Jing
    If this post helps, please Accept it as Solution to help other members find it. Highly appreciate your Kudos!

  • clo_64's avatar
    clo_64
    Frequent Visitor
    3 years ago

    Thanks for the code! Great stuff with the ranking... I'm pretty good in DAX but M is still new to me