Forum Discussion

mariuspetcu's avatar
mariuspetcu
Regular Visitor
6 years ago
Solved

Create new tables based on a column in parent table

Dear all,

 

I am having a table with over 1 milion values where are stored some logs.

 

 

For each "ParentID" i want to create a new table that contain only the values that belong to a certain parentID from the columns "DateTimeStamp" and "OdometerValue".

 

Please help with some indications.

 

Thanks in advance.

Need Help
  • v-lid-msft's avatar
    v-lid-msft
    6 years ago

    Hi mariuspetcu ,

     

     We can use the following function formula to generate the Query which will get the split table automatically for given ID.

     

    let
    Source = (GetID as number) => let
        Source = Table.SelectRows(Table, each ([ID] = GetID))
    in
        Source
in
    Source

     

     

    Each time you use this function, it will generate a table for given ID, the small table will be automatically splited from whole table every time.

     

    But we did not find a way to generate all table for every IDs at the same time.

     


    BTW, pbix as attached.

     

    Best regards,

    Community Support Team _ Dong Li
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

7 Replies

    • Nathaniel_C's avatar
      Nathaniel_C
      Icon for Community Champion rankCommunity Champion
      6 years ago

      Hi mariuspetcu , amitchandak ,

      I don't know of any way to automatically create multiple new tables based on ParentID. You can do this in Power Query, by copying the table, removing unwanted columns, then filtering for each ParentID and then saving the table. A lot of work! Perhaps you can create a macro in VBA to run against a list of all the values in ParentID. jdbuchanan71 ? Any thoughts?


       

       

       




      Let me know if you have any questions.

      If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos are nice too.
      Nathaniel

      • mariuspetcu's avatar
        mariuspetcu
        Regular Visitor
        6 years ago

        Dear amitchandak Nathaniel_C 

         

        Thank you very much for your replys. The sollution with summarize provided by amitchandak is not a viable one because it doesn't bring every value in the new table. In the same time the solution from Nathaniel_C is a good solution, but as him said not an automaticaly one.


        Let me provide more info. In the first time here is the download link for the .pbix file.

         

        The table tbLogTimesValues contains data from a lot of sensors / meters, that read a value mainly each 15 minutes. Each parent ID is a log (series of readings) from an individual sensor. 

        As you can see in the .pbix file, the table contain for now around 1.5 million values. The main chalange is than this table is connected to a SQL database, and the number of record will grow over time. So the option with spliting the table manually is not really the best ideea.

        In my opinion, the best way to work with the data from that table is to split in tables that contains only the numbers for each certain sensor/meter, and also update automatically once with the database growing.

         

         

        I have a hunch (or maybe hope), that if it is possible to have an visualisation, like the one in the fille attached, that is able to separate those values, there must be somehow a way to transfer those numbers in a new table autommaticaly, not only by manually filtering.

         

        Thank you.

         

         

  • Nathaniel_C's avatar
    Nathaniel_C
    Icon for Community Champion rankCommunity Champion
    6 years ago

    Hi mariuspetcu , amitchandak 

    "For each "ParentID" i want to create a new table that contain only the values that belong to a certain parentID from the columns "DateTimeStamp" and "OdometerValue"."

    And thinking about this further, I don't think that you want to do this for each parent ID. Even if each Parent ID had a hundred rows, why would you want 100,000 tables?

    Nathaniel