cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
irfantriatmojo
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
BA_Pete
Super User
Super User

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

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

negi007
Super User
Super User

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

 

negi007_0-1600696898682.png

negi007_1-1600696912233.png

 




Did I answer your question? Mark my post as a solution!
Appreciate your Kudos


Proud to be a Super User!

Follow me here


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")



Did I answer your question? Mark my post as a solution!
Appreciate your Kudos


Proud to be a Super User!

Follow me here


Helpful resources

Announcements
2022 Release Wave 1 760x460.png

2022 Release Wave 1 Plan

Power Platform release plan for the 2022 release wave 1 describes all new features releasing from April 2022 through September 2022.

Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

Top Solution Authors