cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Frequent Visitor

Grouping data model

Hi Everyone

 

i have record like this 

Unique NumberResi Number
A001R001
A001R002
A002R002
A002R003
A003R003
A003R004
A004R005
A005R006

i wanna create a group of data who relate between Unique Number and Resi Number.

Unique NumberResi NumberGroup
A001R001A001
A001R002A001
A002R002A001
A002R003A001
A003R003A001
A003R004A001
A004R005A004
A005R006A005

A002-A003 is a group A001 because they have relation and A004 & A005 is a group by its own because they don;t have any relation.

 

how it can be in Power BI?

 

Thank you

1 ACCEPTED SOLUTION
Super User II
Super User II

Hi @irfantriatmojo ,

 

The most efficient way to calculate and store this by creating a dimension table in Power Query.

In Power Query, go to New Source>Blank Query then in Advanced Editor paste my code over the default code. You can then follow the steps I took to complete this:

let
    yourTable = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcjQwMFTSUQoCUbE6yHwjGN8IO98YxjfGzjeB8U0gfFMY3xTCN1OKjQUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Unique Number" = _t, #"Resi Number" = _t]),
    Source = Table.Distinct(Table.SelectColumns(yourTable, "Unique Number")),
    addNumberGroup = Table.AddColumn(Source, "Number Group", each if [Unique Number] = "A001" then "A001" else if [Unique Number] = "A002" then "A001" else if [Unique Number] = "A003" then "A001" else [Unique Number], type text)
in
    addNumberGroup

 

Summary:

1) Create a distinct list of your [Unique Number] field from your original table ("yourTable")

2) Add a conditional column with the grouping criteria

3) You would then relate [Unique Number] in your dimension table to [Unique Number] in yourTable, using One to Many respectively

4) You would use the [Number Group] field from the dimension table any time you wanted to display grouped values

 

This has the following advantages:

1) It passes the calculation/transformation work to Microsoft as part of the Power BI Service refresh process

2) It only adds a distinct-list table to your model to be loaded into memory, rather than a potentially huge, indistinct, calculated column.

 

Now, you mentioned that the grouping is defined by some sort of relationship between the [Unique Number] values. If you can provide further details around these relationships and if the logic is stored in your data somewhere, we can have a look at making this more 'automatic' as you have also mentioned.

 

Pete

View solution in original post

4 REPLIES 4
Super User II
Super User II

Hi @irfantriatmojo ,

 

The most efficient way to calculate and store this by creating a dimension table in Power Query.

In Power Query, go to New Source>Blank Query then in Advanced Editor paste my code over the default code. You can then follow the steps I took to complete this:

let
    yourTable = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcjQwMFTSUQoCUbE6yHwjGN8IO98YxjfGzjeB8U0gfFMY3xTCN1OKjQUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Unique Number" = _t, #"Resi Number" = _t]),
    Source = Table.Distinct(Table.SelectColumns(yourTable, "Unique Number")),
    addNumberGroup = Table.AddColumn(Source, "Number Group", each if [Unique Number] = "A001" then "A001" else if [Unique Number] = "A002" then "A001" else if [Unique Number] = "A003" then "A001" else [Unique Number], type text)
in
    addNumberGroup

 

Summary:

1) Create a distinct list of your [Unique Number] field from your original table ("yourTable")

2) Add a conditional column with the grouping criteria

3) You would then relate [Unique Number] in your dimension table to [Unique Number] in yourTable, using One to Many respectively

4) You would use the [Number Group] field from the dimension table any time you wanted to display grouped values

 

This has the following advantages:

1) It passes the calculation/transformation work to Microsoft as part of the Power BI Service refresh process

2) It only adds a distinct-list table to your model to be loaded into memory, rather than a potentially huge, indistinct, calculated column.

 

Now, you mentioned that the grouping is defined by some sort of relationship between the [Unique Number] values. If you can provide further details around these relationships and if the logic is stored in your data somewhere, we can have a look at making this more 'automatic' as you have also mentioned.

 

Pete

View solution in original post

Super User II
Super User II

@irfantriatmojo  You can create group in your data. Like below

 

negi007_0-1600696898682.png

negi007_1-1600696912233.png

 

can it create group automatically? 

@irfantriatmojo  You can create a calculated column in this case. However, you will need to build a logic in your calculation like below

 

Group_Col = if (INT(RIGHT('Group'[Unique Number],3))<4, "Group1", "Group2")

Helpful resources

Announcements
secondImage

Happy New Year from Power BI

This is a must watch for a message from Power BI!

December Update

Check it Out!

Click here to read more about the December 2020 Updates!

Community Blog

Check it Out!

Click here to read the latest blog and learn more about contributing to the Power BI blog!

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors
Top Kudoed Authors