cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Syndicate_Admin
Administrator
Administrator

Create New Table Displaying Unique Values From EACH Column - ignoring row relationships

I am trying to create a slicer that works across all datasets - these datasets are ever changing so I need a seperate table which generates unique values on refresh within Power Query - whilst ignoring row relationships

 

EXAMPLE

 

Input

Unique Product IDManufacturing Plant Material TypeMaterial GroupCountry
45600A08WSAAUK
45601B04WSAARO
45602A02WSAAPO
45603B04WABBUK
45604A06WSAAUK

 

Transformation

 

Manufacturing Plant Material TypeMaterial GroupCountry
A02WAAAPO
A06WSBBRO
A08  UK
B04   
    

 

Any help would be appreciated, I've tried many options but can't seem to remove the row relationship

1 ACCEPTED SOLUTION

Accepted Solutions
Syndicate_Admin
Administrator
Administrator

Hi @philippajohnson ,

 

It sounds like you need to create dimension tables for each of the dimensions that you want to slice on.

You can create these dynamically in Power Query by using the following code in a new blank query:

let
    Source = Table.Distinct(Table.SelectColumns(inputTable, "Manufacturing Plant"))
in
    Source

 

Do this for each of the columns that you want to be able to slice against, apply all the tables to your data model, then relate dimensionTable[columnName] to factTable[sameColumnName] on a 1:MANY basis.

Use the dimension table column in any filters/slicers and it will correctly filter your fact table.

 

The principle you are looking for is STAR SCHEMA, and more info can be read here:

https://docs.microsoft.com/en-us/power-bi/guidance/star-schema 

 

Pete

View solution in original post

4 REPLIES 4
Syndicate_Admin
Administrator
Administrator

Hi @philippajohnson ,

 

It sounds like you need to create dimension tables for each of the dimensions that you want to slice on.

You can create these dynamically in Power Query by using the following code in a new blank query:

let
    Source = Table.Distinct(Table.SelectColumns(inputTable, "Manufacturing Plant"))
in
    Source

 

Do this for each of the columns that you want to be able to slice against, apply all the tables to your data model, then relate dimensionTable[columnName] to factTable[sameColumnName] on a 1:MANY basis.

Use the dimension table column in any filters/slicers and it will correctly filter your fact table.

 

The principle you are looking for is STAR SCHEMA, and more info can be read here:

https://docs.microsoft.com/en-us/power-bi/guidance/star-schema 

 

Pete

Syndicate_Admin
Administrator
Administrator

Hi @philippajohnson 
It sounds like ou are trying to create several Bridging tables for your Data Model from multiple Fact Tables that share deminsions.  To create a Distinct list for each dimension, you need to use the "Drill Down" tool in Power Query.  If you Right-Click on a Column and choose either "Drill Down" to "Add as New Query".  From the result, you can then use the "Remove Duplicates" tool.

From here the best thing to do is to understand the M Language generated.
Source = #"Original Table"[Original Column],
#"Remove Duplicates" = List.Distinct(Source)

The reason for this is you want add the other table column lists using the "List.Combine" function.  Sorry no button for this.

Source = List.Combine( { #"Original Table"[Original Column] , #"Other Table"[Original Column] , "etc" , "etc" } ),
#"Remove Duplicates" = List.Distinct(Source)

This approach might lead to performance issue if the query folding becomes an issue.  At this point, you may to pull the column lists into individual queries and then merge.  There is concept call List.Buffer that may need to be added.

 

 

Thank you so much for taking the time to reply, this has worked perfectly *chefs kiss* 

H Daryl, I am sure this works but I am too stupid to make it happen 😞 

 

Thank you taking the time to reply - and perhaps someone smarter will benefit from it more

Helpful resources

Announcements
Microsoft 365 Conference – December 6-8, 2022

Microsoft 365 Conference – December 6-8, 2022

Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.

Difinity Conference 2022

Difinity Conference 2022

Register today for two amazing days of learning, featuring intensive learning sessions across multiple tracks, led by engaging and dynamic experts.

European SharePoint Conference

European SharePoint Conference

The European SharePoint Conference returns live and in-person November 28-December 1 with 4 Microsoft Keynotes, 9 Tutorials, and 120 Sessions.

Power Apps Ideas

Changes to Ideas Coming

We are excited to announce a new way to share your ideas for Power Apps!

Top Solution Authors
Top Kudoed Authors
Users online (4,449)