cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
mariuspetcu
Regular Visitor

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.

 

1.JPG

 

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.

1 ACCEPTED SOLUTION

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

 

4.PNG5.PNG6.PNG

 

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.

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.

View solution in original post

7 REPLIES 7
Nathaniel_C
Super User
Super User

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





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




amitchandak
Super User
Super User

Try summarize

https://docs.microsoft.com/en-us/dax/summarize-function-dax

 

The information you have provided is not making the problem clear to me. Can you please explain with an example. If possible please share a sample pbix file after removing sensitive information.
Thanks

 

 

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!! !!
Dashboard of My Blogs !! Connect on Linkedin
Want To Learn Power BI
Learn Power BI Beginners !! Advance Power BI Concepts !! Power BI For Tableau User !! Learn Power BI in Hindi !!
Proud to be a Super User!

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?


newTABLE12.PNG

 

newTABLE.PNG

 

newtable1.PNG

 




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





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




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.

 

1.JPG

 

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.

2.JPG

 

Thank you.

 

 

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

 

4.PNG5.PNG6.PNG

 

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.

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.

View solution in original post

Hi @v-lid-msft,


It works perfectly, is not an inconvenient that every small table have to be individualy created, as much time as it is automatically updated.

 

Meanwile, i've found a second option. Thinking about what @Nathaniel_C  said about "How much data is to much data" i found a way to split the table direcly in the source database, with a query, that have the posibility to generate all the tables you need (you have to repeat how much you need the code below).

 

drop table [NewDB].dbo.LogName
SELECT *
INTO [NewDB].dbo.LogName
FROM [ParentDB].dbo.tbLogTimeValues
WHERE ParentID=199;
ALTER TABLE [NewDB].dbo.LogName
DROP COLUMN SeqNo,FloatVALUE,ParentID

 

In my opinion, the time allocated to split it with your method in PowerBI could be equivalent with the time allocated to write the query.

 

In the example above i've chosen to have the child tables in a new database, just for easier operability.

 

In the same time, just like the table splitting to be refreshed automatic you can add a scheduled job in SQL server agent, or using a .bat file that execute the query with the frequency you need (if you use SQLExpress).

 

But, the method that you provided is way better than complicating with a lot of other things.

Thank you very much.

Hi @mariuspetcu , @amitchandak ,

So, it is more a matter of how much data is too much in a SQL database, right? You seem to being doing ok from your pictures of creating visualizations, which is what Power BI is all about anyway, distilling data to a reasonable view. How much data is out of my area of knowledge.  Maybe we can get @MattAllington to give some feedback.  He is very knowledgeable and has written some great books, including Supercharge Power BI.
Thank you,

Nathaniel





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Helpful resources

Announcements
Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

Jan 2022 Dev Camp 768x460 copy.png

Power BI Dev Camp- January 27th, 2022

Mark your calendars and join us for our next Power BI Dev Camp!

UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Top Solution Authors
Top Kudoed Authors