Forum Discussion
Data not passing through dataflow
Hi Kody,
Table visuals in Power BI automatically aggregate similar values to the fewest possible rows within the data context.
In your example, if you put the column that contains "HBA of Winton-Salem" (let's call it [Column1]) into a table visual, you would just get a single row with that value in it. However, if you then added one of the columns from the merged table that has a different value for each instance of "HBA of Winton-Salem" ([Column2]), the table visual will duplicate "HBA of Winton-Salem" in order to display all the different [Column1] : [Column2] combinations.
This behaviour is by design and makes perfect sense in all but the most edge-case of reporting scenarios.
As an aside, merging tables to create larger tables with duplicated rows/values in this way is inefficient in processing resources, file size, and model responsiveness. The most efficient way to handle this scenario would be to send both tables to the data model unmerged, then create a relationship between the two tables on the field that you previously merged them on (it looks like this relationship would default to Table1 ONE : MANY Table2).
This will give you the same functionality and usability of the data, but will speed up refresh times by removing the processing overhead required for the merge, reduce your file size by removing duplicated values due to the merge, and improve model response times by Power BI being able to scan smaller tables.
Pete
Hi Kody,
Table visuals in Power BI automatically aggregate similar values to the fewest possible rows within the data context.
In your example, if you put the column that contains "HBA of Winton-Salem" (let's call it [Column1]) into a table visual, you would just get a single row with that value in it. However, if you then added one of the columns from the merged table that has a different value for each instance of "HBA of Winton-Salem" ([Column2]), the table visual will duplicate "HBA of Winton-Salem" in order to display all the different [Column1] : [Column2] combinations.
This behaviour is by design and makes perfect sense in all but the most edge-case of reporting scenarios.
As an aside, merging tables to create larger tables with duplicated rows/values in this way is inefficient in processing resources, file size, and model responsiveness. The most efficient way to handle this scenario would be to send both tables to the data model unmerged, then create a relationship between the two tables on the field that you previously merged them on (it looks like this relationship would default to Table1 ONE : MANY Table2).
This will give you the same functionality and usability of the data, but will speed up refresh times by removing the processing overhead required for the merge, reduce your file size by removing duplicated values due to the merge, and improve model response times by Power BI being able to scan smaller tables.
Pete
Hi Pete,
I really appreciate the feedback. I had originally thought it would be easiest to do the merge since we are bringing very little date in from the second table, but you explained it really well. This is how I had originally tried doing it, but need to gain more knowledge on working with relationships. Merging the table was my "easy workaround", but I see now I was using ducktape to plug the holes on my boat! Thanks again for taking the time to write out a detailed response!
