cancel
Showing results for 
Search instead for 
Did you mean: 

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

Reply
MEAlexander33
New Member

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.  Screenshot 2022-09-13 144727.png

1 ACCEPTED SOLUTION
v-stephen-msft
Community Support
Community Support

Hi @MEAlexander33 ,

 

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

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

vstephenmsft_0-1663148270004.png

vstephenmsft_1-1663148347989.png

vstephenmsft_2-1663148357557.png

 

2.Add a custom column to add the index.

vstephenmsft_4-1663148426416.png

vstephenmsft_3-1663148414526.png

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

vstephenmsft_5-1663148519469.png

vstephenmsft_6-1663148614787.png

vstephenmsft_7-1663148620565.png

 

 

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.

 

 

 

 

View solution in original post

4 REPLIES 4
v-stephen-msft
Community Support
Community Support

Hi @MEAlexander33 ,

 

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

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

vstephenmsft_0-1663148270004.png

vstephenmsft_1-1663148347989.png

vstephenmsft_2-1663148357557.png

 

2.Add a custom column to add the index.

vstephenmsft_4-1663148426416.png

vstephenmsft_3-1663148414526.png

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

vstephenmsft_5-1663148519469.png

vstephenmsft_6-1663148614787.png

vstephenmsft_7-1663148620565.png

 

 

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
Super User
Super User

=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
Super User
Super User

@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?


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

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.   

Helpful resources

Announcements
PBI November 2023 Update Carousel

Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

Community News

Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

Power BI Fabric Summit Carousel

The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors