Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
Solved! Go to 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
Proud to be a Datanaut!
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
Proud to be a Datanaut!
@irfantriatmojo You can create group in your data. Like below
Proud to be a Super User!
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
Proud to be a Super User!
User | Count |
---|---|
130 | |
108 | |
101 | |
67 | |
62 |
User | Count |
---|---|
137 | |
115 | |
102 | |
71 | |
61 |