Forum Discussion
Duplicated Values - Need Assistance
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.
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.
4 Replies
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?
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.
=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]))
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.
