How do you create a unique row value combination ID to a table?

Question

Hi Guys&nbsp;You never fail to help out with a powerbi "puzzle" 😄&nbsp;I've got a dimension table with the following columns:&nbsp;Dimension_IDDepartmentBusiness areaFunctionTypeUnique_ID1Adminitrationnullnullnull?1Airnullnullnull?1nullSeanullnull?1nullnullImportnull?1nullnullnullForwarding?1Air/Sea/Railnullnullnull?1nullnullDomesticnull?2Seanullnullnull?2Railnullnullnull?2Continentnullnullnull?2nullContinent Roadnullnull?2nullnullExportnull?2nullnullnullForwarding?2nullnullDomesticnull?&nbsp;As you can see the dimension id's i get are the same for different combination of row values.&nbsp;&nbsp;what&nbsp; i want is to create a unique_id column that gives a unique id to every row combination that is unique.Next question is, the Dimension ID is the relation to the fact table. I can't remove duplicates in that column then i'll loose a lot of row value combinations that i need, and if i create this Unique_ID column i'd want it to become the relationship tie between the dimension and fact table, but not sure that is possible.&nbsp;Any idea how i can untangle this dilemma?

anonymous · Accepted Answer

Hi&nbsp;MacJasem&nbsp;,See if these two columns meet the requirements:
Unique_ID 1 = CONCATENATE(CONCATENATE(CONCATENATE(CONCATENATE([Dimension_ID], [Department]), [Business area]), [Function]), [Type])Unique_ID 2 = RANKX(ALL('Table'), CONCATENATE(CONCATENATE(CONCATENATE(CONCATENATE([Dimension_ID], [Department]), [Business area]), [Function]), [Type]), , , Dense)
Output:

Best Regards,GaoCommunity Support Team
&nbsp;
If there is any post&nbsp;helps, then please consider&nbsp;Accept it as the solution&nbsp;&nbsp;to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know.&nbsp;Thanks a lot!
How to get your questions answered quickly&nbsp;--&nbsp;&nbsp;How to provide sample data in the Power BI Forum

Forum Discussion

How do you create a unique row value combination ID to a table?

1 Reply

Related content

Create 3 digit sequential unique key

Create unique ID

Combine Rows only keeping unique values

Tables combine

Unique Combinations

Dimension_ID	Department	Business area	Function	Type	Unique_ID
1	Adminitration	null	null	null	?
1	Air	null	null	null	?
1	null	Sea	null	null	?
1	null	null	Import	null	?
1	null	null	null	Forwarding	?
1	Air/Sea/Rail	null	null	null	?
1	null	null	Domestic	null	?
2	Sea	null	null	null	?
2	Rail	null	null	null	?
2	Continent	null	null	null	?
2	null	Continent Road	null	null	?
2	null	null	Export	null	?
2	null	null	null	Forwarding	?
2	null	null	Domestic	null	?