Duplicated Values - Need Assistance

[MEAlexander33]

Hi all.  

I am rather new with Power BI and have a quick question for you all.   I have a data set where in one column there are a couple of duplciated values.   It is a financial data set, where for example, we have two CAM accounts, one for store level and one for G&A.   In the data set I am using, unforuntalty I only have access to account descriptions rather than GL accounts.  In the CAM section, I need a way to identify the first CAM account as store level and the second as G&A, that way I only have unique accounts in my data set.   Below is an example of how the data set is layed out.  

[v-stephen-msft · Accepted solution]

Hi @MEAlexander33 ,

You could add an index column group by the CAM column.

1.Group by, select All rows in the option.

2.Add a custom column to add the index.

3.Expand the column and create a custom column to return store level or G&A.

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[wdx223_Daniel]

=Table.ReverseRows(Table.Combine(Table.Group(Table.ReverseRows(PrevousStepName),"account_description",{"n",each Table.SplitColumn(Table.Skip(_),"account_description",(x)=>{[account_description]{0},x},{"level1","level2"}),0,(x,y)=>Byte.From(Text.StartsWith(y,"Total")))[n]))

[Greg_Deckler]

@MEAlexander33 So if it is a matter of order, you will need to add an Index so you can identify "first". Otherwise, what is the criteria for how you would identify which is which?

[MEAlexander33]

Yes it is a matter of order.  In my example, the first CAM identified for each time_key and store_id would be the store level acocunt and the second would the the G&A level account.