Grouping data model

[irfantriatmojo]

Hi Everyone

i have record like this 

Unique Number	Resi Number
A001	R001
A001	R002
A002	R002
A002	R003
A003	R003
A003	R004
A004	R005
A005	R006

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

Unique Number	Resi Number	Group
A001	R001	A001
A001	R002	A001
A002	R002	A001
A002	R003	A001
A003	R003	A001
A003	R004	A001
A004	R005	A004
A005	R006	A005

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

[BA_Pete · Accepted solution]

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]

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

[irfantriatmojo]

can it create group automatically? 

[negi007]

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