Importing table with summary rows

[kjerez]

Hello

I need help importing a table that has emdebed rows with summary information, see  the example bellow:

As you can see the first appearence of the observation in column group is the summary of the subsequent observations within the same gruop. However not all the the observations have subgroups in (see the last row), in that case I would like to have that row included, which means I can not solely filter by subgroup. 

Any idea on how to import this to power Bi? 

Thanks

[amitchandak · Accepted solution]

@kjerez , Create a new column like

new column =
var _Sub = countx(filter(Table,[group] =earlier([group]) && not(isblank([sub group]))),[sub group])+0
if(_sub >0, "Has sub group", "No Sub group")

[amitchandak · Accepted solution]

@kjerez , Create a new column like

new column =
var _Sub = countx(filter(Table,[group] =earlier([group]) && not(isblank([sub group]))),[sub group])+0
if(_sub >0, "Has sub group", "No Sub group")

[kjerez · Accepted solution]

Thanks,

I ended up using:

Column = var _Sub = COUNTX(FILTER('All Types',[Group Name]=EARLIER([Group Name]) && NOT(ISBLANK([SubGroup]))),[SubGroup])+0 return if(_sub >0, "Has sub group", "No Sub group")

I also create another dummy variable with subgroup, and created a filter combining these two.