Power BI Parent and Child Sequential data issue

[rouelandrew]

Need help with transforming data in PowerBI. Data is sequential and has a Parent, Child relationship.

1. Sample is in Excel but data is in Smartsheets which I can't manipulate using SQL
2. I've connected PowerBI to Smartsheets and is directly pulling the data from there
3. it contains 1 row for each Parent and that Parent row would have a few columns filled up, but new events within that Parent would be in a Child row, which would have other columns filled up but would have the Customer Name, Customer #, etc. show as blank.
4. Some parent rows would not have other Child rows or events under it
5. Data updates on a regular basis

Because of this, I am having trouble visualizing the data using those columns and filtering by the Customer specific data. The data on columns like Issue or Updates & History would show blank. Of course, in Excel this is doable by manual copying the Customer specific data but we need the dashboard to update as the data in Smartsheets also get updated. Can anyone help me out?

Sample sequential data

[VillyMBI · Accepted solution]

Hi @rouelandrew ,

if you have the same data sheet, you can do it in Power Query by using the option "Fill" down

With the fill-down option, data will be populated in the records below in the same column until the next parent record (not a null value) is found.

[rouelandrew]

Hey thanks,

Do I only need to do this one time and further updates will be automatic? Or do I have to keep doing it everytime there are new rows added to the data?

[VillyMBI]

It is a one time job.

[talespin]

hi @rouelandrew 

This is more of a Power Query thing, There are ways in Power Query that you can use to populate parent info against each child record.

If you can copy data in table form rather than image, I can give it a try. 

[123abc]

To handle sequential data with parent-child relationships in Power BI, you can follow these steps to transform the data and visualize it effectively:

1. Load the data into Power BI: You've already connected Power BI to Smartsheets and loaded the data. Ensure that all relevant columns are imported, including those for Parent and Child relationships.

2. Identify parent and child rows: Power BI doesn't inherently recognize parent-child relationships like a database management system would. You need to establish this relationship within Power BI based on the structure of your data.

3. Transform the data: Use Power Query Editor to transform the data. You'll want to do the following:

   • Identify parent rows and child rows.
   • Merge the child rows with their corresponding parent rows using a unique identifier.
   • Fill in the blank fields in child rows with the relevant information from their parent rows.

4. Create calculated columns if needed: If you need to concatenate or manipulate data from multiple columns to create new fields, you can do so using DAX (Data Analysis Expressions) in Power BI.

5. Build visualizations: Once your data is prepared, you can create visualizations such as tables, charts, or graphs to represent the data. You can filter the data based on customer-specific information using slicers or filters.

6. Ensure data refresh: Since your data updates regularly in Smartsheets, make sure that Power BI is set up to refresh the data automatically. You can schedule data refreshes in the Power BI service to ensure that your dashboard reflects the most recent data from Smartsheets.

7. Testing and validation: Test your dashboard to ensure that it accurately reflects the data from Smartsheets. Pay attention to any discrepancies or errors that may occur during the transformation process.

By following these steps, you should be able to effectively transform your sequential data with parent-child relationships from Smartsheets and visualize it in Power BI. If you encounter any specific challenges during the process, feel free to provide more details, and I can assist you further.