Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
47 | |
24 | |
20 | |
15 | |
13 |
User | Count |
---|---|
55 | |
48 | |
43 | |
19 | |
19 |