Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.
Dear member,
could you please kindly help me to solve the following problem:
I would like to get this result in table of power.
Best regards
Chi
Solved! Go to Solution.
HI @ChiRomeu
Usually this requires an index column easily added using power query with one click. However, if this is not possible for any reason you can create a new calculated table as follows. (refer to attached sample file)
Where 'Data' is the original table
Data New =
VAR Items = CONCATENATEX ( Data, Data[MasterId], "|" )
VAR Length = PATHLENGTH ( Items )
VAR T1 = GENERATESERIES ( 1, Length, 1 )
VAR T2 = ADDCOLUMNS ( T1, "@Index", [Value], "@MasterID", PATHITEM ( Items, [Value] ) )
VAR T3 =
ADDCOLUMNS (
T2,
"@UniqueValue",
VAR CurrentID = [@MasterID]
VAR CurrentIndex = [@Index]
VAR FirstIndex = MINX ( FILTER ( T2, [@MasterID] = CurrentID ), [@Index] )
RETURN
IF ( CurrentIndex = FirstIndex, 1, 0 )
)
RETURN
SELECTCOLUMNS ( T3, "Master ID", [@MasterID], "Unique Value", [@UniqueValue] )
HI @ChiRomeu
Usually this requires an index column easily added using power query with one click. However, if this is not possible for any reason you can create a new calculated table as follows. (refer to attached sample file)
Where 'Data' is the original table
Data New =
VAR Items = CONCATENATEX ( Data, Data[MasterId], "|" )
VAR Length = PATHLENGTH ( Items )
VAR T1 = GENERATESERIES ( 1, Length, 1 )
VAR T2 = ADDCOLUMNS ( T1, "@Index", [Value], "@MasterID", PATHITEM ( Items, [Value] ) )
VAR T3 =
ADDCOLUMNS (
T2,
"@UniqueValue",
VAR CurrentID = [@MasterID]
VAR CurrentIndex = [@Index]
VAR FirstIndex = MINX ( FILTER ( T2, [@MasterID] = CurrentID ), [@Index] )
RETURN
IF ( CurrentIndex = FirstIndex, 1, 0 )
)
RETURN
SELECTCOLUMNS ( T3, "Master ID", [@MasterID], "Unique Value", [@UniqueValue] )
Thanks a lot!
Hi:
Assuming your table name = "Data" you can use this caclculated column:
I hope this helps!
Thank you for info.
Is it possible to include the first row about the >2 rows?
best regards
YEs, like this?
If this helps, please mark as solution.Have a nice day!
User | Count |
---|---|
21 | |
19 | |
12 | |
9 | |
8 |
User | Count |
---|---|
30 | |
27 | |
14 | |
13 | |
10 |