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

Sonova

New Member
Joined
Feb 21, 2017
Messages
24
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.

CategoryAllocation
1.1Labour-Tradesmen1
1.1Labour-Tradesmen0
1.2Camp Running1
1.2CampRunning0
4.3CampRunning1

<tbody>
</tbody>
 

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.
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.

TrnsctCategory KeyProjetc J54Related BudgetAllocate
601010042015/12

<tbody>
</tbody>
1.1Labour-Tradesmen

<tbody>
</tbody>
10004001
601010052015/12

<tbody>
</tbody>
1.1Labour-Tradesmen

<tbody>
</tbody>
04000
607010052015/12

<tbody>
</tbody>
1.2Camp Running

<tbody>
</tbody>
20005001
607010482015/12

<tbody>
</tbody>
1.2Camp Running

<tbody>
</tbody>
05000
617010482015/12

<tbody>
</tbody>
1.2Camp Running

<tbody>
</tbody>
05000

<tbody>
</tbody>
 
Upvote 0
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.
 
Upvote 0
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

TRNSCCategoryProject J54Budget(RELATED)Trnsct CountCalculationAllocated Budget
601010042015/121.1Labour-Tradesmen10004005400/580
601010052015/121.1Labour-Tradesmen04005400/50
607010052015/121.2Camp-Running200050010300/1050
607010482015/121.2Camp Running050010300/100

<tbody>
</tbody>
 
Upvote 0
It looks as though the data you posted in post #3 is already summarised?

What is TRNSC, I assumed it was a transaction reference?
 
Upvote 0
Hi
A code for select a first row of each unique category key
Code:
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,
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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
Code:
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,
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,213
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top