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.
Hi,
in the attached sheet, C4 extracts the unique values from C3 with conditions
C1-A && C2-P1in C4 - 500, If C2 is duplicated single value should consider in C4.
Hence the reuired Column is C4 based on C1 & C2
https://drive.google.com/file/d/1l0bGaAX4p8uLjUXupxa2LZRpnvcvQj5R/view?usp=sharing
It returns the following table in Power Query:
It does it by inserting an Index column first, then getting the previous row by referencing the index value - 1.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingCould you please share the file
Thanks, the result is rigth, ist possible to share the Pbix file to understnad better.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingHi @Anonymous ,
Or you could do like this:
First, create a [Index] column in "Edit Queries";
Second, create a calculated column:
Column =
VAR x = MOD([Index], 2)
RETURN
IF(
x = 0,
BLANK(),
[C3]
)
Best regards,
Lionel Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
It need not in a sequence .. the records in C3 will not be in sequence. In that how to resolve this?
Above solution doesn't work
@Anonymous can you be more specific? You keep saying for us to share the file, but my first post has the file link.
THen you keep responding that something isn't working. Can you please tell us EXACTLY what isn't working and what your expected output is? Help us help you. Just saying "it doesn't work" isn't helpful. Our solutions do work if implemented correctly and if we understand your requirements. You have to be very clear about what those requirements are, and what exactly isn't working to give those requirements
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingHi @Anonymous ,
First sort the [C1] column in ascending order, then sort the [C2] column in ascending order, and finally add the [Index] column:
Column = MOD(Sheet3[Index], 2)
Column 2 =
IF(
[Column] = 1,
[C3],
BLANK()
)
Best regards,
Lionel Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @Anonymous ,
The [Index] column is not added for sorting, but to lock the rows in each group (grouped by C1 and C2 columns), because the values of C3 after grouping of C1 and C2 columns are the same, so the condition cannot be judged.This is a convenient calculation method.
Best regards,
Lionel Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
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 |
---|---|
107 | |
98 | |
78 | |
65 | |
60 |
User | Count |
---|---|
148 | |
113 | |
97 | |
84 | |
67 |