Hi all,

My team members are entering data into the below shortened table.

I want to import this table to my model (a different excel file), but I need to manipulate the data in order to use it.

The team members table (MaterialTypeTbl) looks like:

For every Material Type (column B) they enter all the suppliers that can work with us. Suppliers are split by *.

[Note: the * is a symbol I requested them to use, because supplier names can contain commas (which was my preferred option) and I thought that it can create confusion when extracted by query formulas. So basically you can change the * to anything that can promote the solution]

My ideal new table will look like:

Which is repeating the row, as the number of suppliers that we have in the "supplier" cell.

<gs class="GINGER_SOFTWARE_mark" ginger_software_uiphraseguid="3252eae2-a11d-4307-bf26-0f3a1b44a0a3" id="213baf8f-426a-4948-b391-db24cd57b1d7">i.e.</gs>: material 1 (Metal) had 3 suppliers, so in the new table I have 3 rows with only one name in each "supplier" cell.

The purpose is to later on use the new table for pivots.

The source table can have more columns + I need to have the data about the extracted file (file name, item name, date modified, date created, date accessed)

I have a folder which contains several files like this (with one sheet and one table in the same format as the other) and I need to extract <gs class="GINGER_SOFTWARE_mark" ginger_software_uiphraseguid="d628fc42-39e8-48e7-8a9f-6f40141e2acd" id="98e8ebc9-804f-4df5-a790-41176f3c60fd">all</gs> and consolidate to one table in my power pivot model with the desired formation.

Appreciate any help!

(I posted this Q also in the BI Power Query

Hi Amit,

We worked together on a solution with Power Query.

The post is here.