Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi All,
I have a table with column cycle and year data given below:
CYCLE YEAR
201912 2019
201912 2020
Actuals 2019
I want an output table as:
CYCLE YEAR CYCLE-1 YEAR-1
201912 2019 201912 2020
201912 2019 Actuals 2019
201912 2020 201912 2019
201912 2020 Actuals 2019
Actuals 2019 201912 2019
Actuals 2019 201912 2020
TIA
Hi
This looks to be a cartesian (CROSS) join between the entire table and the first two rows.
Try this:
1-In Power Query, duplicate or Reference the original table so you have a copy of it. We'll call this the COPY and the ORIGINAL
2-On the COPY, apply a Filter step to filter out the value of "Actuals" in the first column, so it should now have 2 rows
3-Apply and Close to bring the two tables into the model.
4-Create the result table with the following DAX statement:
RESULT = CROSSJOIN(ORIGINAL, COPY)
This solution uses BOTH Power Query AND DAX as you cannot do a CROSS JOIN in Power Query, and you use PowerQuery to do the filtering. I suppose you could create the COPY table with a FILTER statement in DAX:
COPY = FILTER(ORIGINAL, [Cycle] <> "Actuals")
Hope this helps
But there will bemore data which will be appended below. I guess than hard coding "Actuals" would fail in that case.
As tested, rajulshah's suggestion works on my side.
Could you show me the problem when implementing the method on your model?
Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hello @singh_gagan30,
Please try following dax query:
Cycle CrossJoin =
VAR CycleCrossJoinDummy = SELECTCOLUMNS(Cycles,"Cycle Name",Cycles[CYCLE],"Cycle Year",Cycles[YEAR])
VAR CrossJoinTable = CROSSJOIN(CycleCrossJoinDummy,Cycles)
RETURN FILTER(CrossJoinTable,[Cycle Name]&[Cycle Year]<>[CYCLE]&[YEAR])
Hope this helps.
Hi,
I guess aftre using this query for forming a new table has went into an infinte loop.
Regards
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
64 |
User | Count |
---|---|
135 | |
118 | |
101 | |
71 | |
61 |