Row transform nested values in one column and their corresponding nested values in another column

[talbi]

Faced with the challenge of transforming the following input table using Power Query.

The expected, output table is listed below.

What would be the steps to achieve such transformation.

Thank you

Raw input dat table to import into Power BI

Departments	Cities	Regions	Offices	Countries
Sales	New Deli|Madrid|Brussels	National Capital Region,Castilla Leon,Brussels Capital Region	1,2,3	India,Spain,Belgium
Marketing	San Francisco|Phoenix|Boulder|Boston|Paris	CA,AZ,CO,MA,Île-de-France	a,b,c,d,e	US,US,US,US,FR

Output Table:

[rsbin · Accepted solution]

@talbi ,

You can use a combination of the following:

Split Columns by Delimiter.  For the Cities column you would use "|", and for the others use ",".

Once Split, you can use the Unpivot Columns to get the Columns into Rows.

Then use Filters or other Flags to Remove the unnecessary rows.  Unfortunately, ran out of time to complete the entire manipulation, but hoping this at least gets you started.

Have attached pbix what I started.

EDIT:   Found a better way.  Attached is a revised pbix file.  Please refer to PQ (2).  Have only done it for Sales, but hope you can see the pattern to replicate the process.

Kind Regards,