Creating a new table which repeats rows RRS feed

  • Question

  • 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 forum)


    Monday, February 16, 2015 12:19 PM


  • Hi Amit,

    We worked together on a solution with Power Query.

    The post is here.

    • Proposed as answer by Gil RavivMVP Thursday, March 12, 2015 8:09 AM
    • Marked as answer by Michael Amadi Friday, March 20, 2015 10:08 AM
    Thursday, March 12, 2015 6:27 AM