Hi again,
I've created a Power BI tool to analyse a rather large spreadsheet (currently goes from A1 to AO6461 & expands every month) & some of the columns have cells with multiple values that are separated by a comma. E.g. for the column "Incident Type", if there's more than one incident, then they're put as "Physical assault,Verbal assault,Damage to property". Same goes for "Hold Type", which has things like "Armchair descent,Figure four" & so on.
The first column in the spreadsheet is the ID column, which is always unique. The 2 columns in question that have the delimiter are further along. For the "Incident type" column, I went into "Transform data" & used the Split column by delimiter & under "advanced" I chose by rows. This works a treat for doing just the one. But when I've tried doing this for the column "Hold type" I've discovered that because the "Incident Type" column is already expanded, it affects the results for the "Hold type". E.g. if ID1 (from the first column named ID) has 3 different incident types but just 1 hold type was used, then split column by delimiter makes 3 rows for each incident type, but then puts the hold type on each of those rows, making it count 3 incidents for that type.
Would creating tables within PBI be the solution? I'd be fine fixing this in Excel if it was the only program I was using, but not sure how to in PBI. Any help would be greatly appreciated.
Thanks
Graeme
