I can´t create a table withou create a circular dependency

[PirataFabri]

I need help with Power BI. Let me try to explain.

I have a table called WorkItems (coming from Azure). Here, I have one record for each WorkItemID. I also have another table (Cadastro) named FluxoPadrao. In this table, I have all the mandatory States for each WorkItemType (this field also exists in the WorkItems table).

I need to create a table called FA_WorkItems (FA = Flow Adherence). In this table, I need to have multiple records for each WorkItemId with State = Closed in the WorkItems table.

The number of records should be equal to the number of records (states) found in the FluxoPadrao table for the same WorkItemType.

The FA_WorkItems table should have the following format: WorkItemID, State.

Attached images might be helpful.

I've tried everything, but I always end up with some circular dependency problem.

[PirataFabri]

Work Items table

FluxoPadrao Table

------------
Desired FA_WorkItems Table

--------------
WorkItemRevisions Table

[tharunkumarRTK · Accepted solution]

@PirataFabri 

1. Merge WorkItems table with FluxoPadrao table (Join on WorkitemType) and retrieve different states of each workitem type)

2. Merge the result (from point 1) with WorkItemRevisions table (Join on WorkitemId) and retieve StatechangeDate. 

To achieve your desired output (with StateInDate and StateOutDate), I think you need to give an index column for both Workitems table and FluxoPadrao Table before performing the above merge operations. If you need any further help, please share the pbix file along with the sample data file.

If the post helps please give a thumbs up

If it solves your issue, please accept it as the solution to help the other members find it more quickly.

Tharun

[PirataFabri]

Worked perfectly. I was already able to create all the table I needed. Thank you very much!

[Greg_Deckler]

@PirataFabri Can you post sample data as text?

[PirataFabri]

I am trying to post the data here. But it is not working