Forum Discussion

rnagallatav's avatar
rnagallatav
New Member
4 years ago
Solved

Need Help - Duplicate rows issue

We have a requirement in power BI to create a tabular visual with columns from table A (id,status,type,value) and columns from table B (S_Opening_date,S_Closer_date) For this I need to create two ca...
Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    Hi  rnagallatav ,

    Here are the steps you can follow：

    1. Enter the power query, Home – Merge Queries – Merge Queries as new – Left Out.

    Result:

    2. Create calculated column.

    Index =
IF(
    'Merge1'[Table B.attribute Title]=BLANK()&&'Merge1'[Table B.attribute value]=BLANK(),0,
RANKX(FILTER(ALL(Merge1),'Merge1'[ID ]=EARLIER('Merge1'[ID ])&&'Merge1'[Status ]=EARLIER('Merge1'[Status ])),[Table B.attribute value],,DESC,Dense))

    3. Put [Index] in Filter, is=1.

    4. Result:

     

    Best Regards,

    Liu Yang

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

HotChilli's avatar
HotChilli
Community Champion
4 years ago

There are no duplicate rows.  There's probably a relationship created on ID and there are 2 entries in Table B for some of the entries in Table A.

-----

To get what you want, I would use Table B as the dimension table and Table A as the Fact table.  It looks like you can Pivot (from the Transform menu) the 'attribute Title' in Table B to get the table you want.

You won't need the calculated columns anymore.

You can then make the relationship from Table B->Table A as 1:m and create the table visual.

--

That will probably work with the sample data but the real data may be more complex.