Forum Discussion

rouelandrew's avatar
rouelandrew
Frequent Visitor
2 years ago
Solved

Power BI Parent and Child Sequential data issue

Need help with transforming data in PowerBI. Data is sequential and has a Parent, Child relationship.   Sample is in Excel but data is in Smartsheets which I can't manipulate using SQL I...
  • VillyMBI's avatar
    VillyMBI
    2 years ago

    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.

123abc's avatar
123abc
Icon for Community Champion rankCommunity Champion
2 years ago

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.