Allocate 1 or 0 to each unique record(string) in column

[Sonova]

Hi

I have been trying for quite some time to find the first unique record in a column and either allocate a "1" if it is or "0" if it is not. I could do this quite easily in excel using a countif but am struggling with powerpivot.

I have tried the follwing formula but it allocates a "1" for every single row in the Category column.

if(calculate(COUNTROWS(JFiveFour),filter(JFiveFour,JFiveFour[Category]=earlier(JFiveFour[Category])))>1,1,0))

The Table below is what I require; to identify the first record as it appears and to allocate a 1 or 0. Any input is greatly appreciated.

[TABLE="width: 500"]
<tbody>[TR]
[TD]Category[/TD]
[TD]Allocation[/TD]
[/TR]
[TR]
[TD]1.1Labour-Tradesmen[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]1.1Labour-Tradesmen[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]1.2Camp Running[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]1.2CampRunning[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]4.3CampRunning[/TD]
[TD]1[/TD]
[/TR]
</tbody>[/TABLE]

 

[Matt Allington]

This is not something you should do in Power Pivot. Read my article here for some tips.
Use Power Query to Compare Database Records - Excelerator BI

 

[Sonova]

Thanks for the reply Matt. I watched the powerquery video but when I remove the duplicates and try to add it to the same table it wont allow it.

To give context why i am trying to do this. I am building an interactive dashboard, my source data is based on transactions my second table includes budget data, I have brought the budget data using the related function(Key is Category key)

But this duplicates the budget value as you can see, this is why I need someway of filtering the duplicates. I just dont know what the best way of going about it is.

[TABLE="width: 500"]
<tbody>[TR]
[TD]Trnsct[/TD]
[TD]Category Key[/TD]
[TD]Projetc J54[/TD]
[TD]Related Budget[/TD]
[TD]Allocate[/TD]
[/TR]
[TR]
[TD]

601010042015/12

<tbody>
</tbody>

[/TD]
[TD]

1.1Labour-Tradesmen

<tbody>
</tbody>

[/TD]
[TD]1000[/TD]
[TD]400[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]

601010052015/12

<tbody>
</tbody>

[/TD]
[TD]

1.1Labour-Tradesmen

<tbody>
</tbody>

[/TD]
[TD]0[/TD]
[TD]400[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]

607010052015/12

<tbody>
</tbody>

[/TD]
[TD]

1.2Camp Running

<tbody>
</tbody>

[/TD]
[TD]2000[/TD]
[TD]500[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]

607010482015/12

<tbody>
</tbody>

[/TD]
[TD]

1.2Camp Running

<tbody>
</tbody>

[/TD]
[TD]0[/TD]
[TD]500[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]

617010482015/12

<tbody>
</tbody>

[/TD]
[TD]

1.2Camp Running

<tbody>
</tbody>

[/TD]
[TD]0[/TD]
[TD]500[/TD]
[TD]0[/TD]
[/TR]
</tbody>[/TABLE]

 

[Comfy]

Instead of filtering the duplicates would apportioning the budget to each transaction work better?

So count all the transactions per Category and then divide the budget.

This won't be accurate at a transnational level but the sub-totals per Category would be.

 

[Sonova]

Hi Comfy, that would work. Do you have any ideas how I would go about that ?

What is the best way to count the number of transaction in a subcategory excluding zeros but should include positive and negatives. The other issue is dividing it by the budget per category.

Final output required

[TABLE="width: 500"]
<tbody>[TR]
[TD]TRNSC[/TD]
[TD]Category[/TD]
[TD]Project J54[/TD]
[TD]Budget(RELATED)[/TD]
[TD]Trnsct Count[/TD]
[TD]Calculation[/TD]
[TD]Allocated Budget[/TD]
[/TR]
[TR]
[TD]601010042015/12[/TD]
[TD]1.1Labour-Tradesmen[/TD]
[TD]1000[/TD]
[TD]400[/TD]
[TD]5[/TD]
[TD]400/5[/TD]
[TD]80[/TD]
[/TR]
[TR]
[TD]601010052015/12[/TD]
[TD]1.1Labour-Tradesmen[/TD]
[TD]0[/TD]
[TD]400[/TD]
[TD]5[/TD]
[TD]400/5[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]607010052015/12[/TD]
[TD]1.2Camp-Running[/TD]
[TD]2000[/TD]
[TD]500[/TD]
[TD]10[/TD]
[TD]300/10[/TD]
[TD]50[/TD]
[/TR]
[TR]
[TD]607010482015/12[/TD]
[TD]1.2Camp Running[/TD]
[TD]0[/TD]
[TD]500[/TD]
[TD]10[/TD]
[TD]300/10[/TD]
[TD]0[/TD]
[/TR]
</tbody>[/TABLE]

 

[Comfy]

It looks as though the data you posted in post #3 is already summarised?

What is TRNSC, I assumed it was a transaction reference?

 

[anvg]

Hi
A code for select a first row of each unique category key

let
    source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    localIdx = Table.Group(source, {"Category Key"} { {"subTable", each Table.AddIndexColumn(_, "idx", 1)} })[[subTable]],
    return = Table.SelectRows(Table.ExpandTableColumn(localIdx, "subTable", Table.ColumnNames(localIdx{0}[subTable])), each [idx] = 1)
in
    Table.RemoveColumns(return, {"idx"})

Regards,

 

[Sonova]

I only have the first 4 columns in my table ,the Trnst Count,Calculation & Allocated Budget are for illustration purpose, ie what i would need in my data table.

 

[Sonova]

It looks as though the data you posted in post #3 is already summarised?

What is TRNSC, I assumed it was a transaction reference?

I only have the first 4 columns in my table ,the Trnst Count,Calculation & Allocated Budget are for illustration purpose, ie what i would need in my data table.

 

[Sonova]

hi anvg thanks for the reply, can you explain in a bit more detail how to implement this, I assume it will go into powerquery but I dont have enough exeperience using that.

Hi
A code for select a first row of each unique category key

let
    source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    localIdx = Table.Group(source, {"Category Key"} { {"subTable", each Table.AddIndexColumn(_, "idx", 1)} })[[subTable]],
    return = Table.SelectRows(Table.ExpandTableColumn(localIdx, "subTable", Table.ColumnNames(localIdx{0}[subTable])), each [idx] = 1)
in
    Table.RemoveColumns(return, {"idx"})

Regards,