Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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

 

 

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.

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.