Power BI Exchange

Please login or click SIGN UP FOR FREE to create your PowerBIUG account to join this user group.

View My Drafts

Back to discussions
Expand all | Collapse all

Automatically Splitting table into smaller ones, based on a column value

  • 1.  Automatically Splitting table into smaller ones, based on a column value

    Posted Jan 14, 2021 06:44 PM

    Hi, 

    I am looking for a way to split a Table into smaller ones, based on the column values. Let's say I have Year in a column and need to create separate tables for each year. I know, it can be done by duplicating a table and filtering it but I wanted to automate it and handle any changes for future in the column value.



    ------------------------------
    Murtaza Hasan
    Business Intelligence Specialist
    ------------------------------


  • 2.  RE: Automatically Splitting table into smaller ones, based on a column value

    Gold Contributor
    Posted Jan 15, 2021 02:32 AM
    Hi,

    In paginated reports it is a common feature but in Power BI it is not really available.
    But there is a preview feature "Small Multiples" which is very close to your requirements. But I think it works only for charts and not for a table visual.
    https://powerbi.microsoft.com/en-us/blog/announcing-small-multiples-public-preview/

    So when you anyway want to use a table visual I think there is no other way to dynamical create additional visuals depending on your data. You can only create them by copy and paste and filter them dynamically on your data. (e.g. for years you do not filter on a specific year but dynamically on current year, previous year etc.)


    Best regards,
    Martin




    ------------------------------
    Martin Frisch
    BI Developer
    Trützschler GmbH & Co. KG
    ------------------------------

    Original Message


  • 3.  RE: Automatically Splitting table into smaller ones, based on a column value

    Posted Jan 15, 2021 06:19 PM
    Thanks Martin. Yes Paginated Report could be an option and then extracting in Excel but again if there are more than 50 fields then parameters would need to be passed 50 times.
    You are, there may not be any solution for this in Power BI, maybe I can work something with macro.

    ------------------------------
    Murtaza Hasan
    Business Intelligence Specialist
    ------------------------------

    Original Message


  • 4.  RE: Automatically Splitting table into smaller ones, based on a column value

    Posted Jan 15, 2021 06:26 PM
    Thank @Ian MacIntosh. Yes referencing is an option but if its more than 50 distinct values, it would be a lot of work and would not be automated. But yes you are right, Power BI isn't built for this. May be I need to use Macros or something else for this. ​

    ------------------------------
    Murtaza Hasan
    Business Intelligence Specialist
    ------------------------------

    Original Message


  • 5.  RE: Automatically Splitting table into smaller ones, based on a column value

    Posted Jan 15, 2021 03:07 AM
    Hi

    This is not recommend - it is best to keep all the data in a single table so you can analyze across years...


    however If you really wanted to,  one way is:

    In PowerQuerry 

    Rather than duplicate the table/query - Reference it. 

    For example - you have a table/query "All Years" and it contains a Column "Year"

    Reference "all years"  , and add a filter on the Years column  = 2020, rename that query/table "2020"

    Reference "all years"  , and add a filter on the Years column  = 2021, rename that query/table "2021"

     etc etc....

    In Report view (Better)

    Create a table in your report, and simply filter the visualization by the year column (or the Year from a related calendar table)

    ------------------------------
    Ian MacIntosh
    Western Australia
    ------------------------------

    Original Message