locked
Create a new table using repeating 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="0f146976-cd66-42bf-a9a9-efb81fa43ab7" id="61366336-c5f8-402a-877c-9c08b112bd3d">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 all and consolidate to one table in my power pivot model with the desired formation.

    Appreciate any help!


    עמית


    • Edited by עמיתת Monday, February 16, 2015 12:16 PM
    Monday, February 16, 2015 11:59 AM

Answers

  • Sorry for the long iterations. I used different samples, which yielded wrong results.

    The following formulas should work now.

    Edit the formula code of getWorkbook according to this section:

    (path) =>
    
    let
        Source = Excel.Workbook(File.Contents(path)),
        Source2 = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
        ToList = Table.TransformColumns(Source2, {"Column5", Splitter.SplitTextByDelimiter("*")}),
        #"Expand Column" = Table.ExpandListColumn(ToList, "Column5"),
        #"First Row as Header" = Table.PromoteHeaders(#"Expand Column"),
        #"Trimmed Text" = Table.TransformColumns(#"First Row as Header",{{"Supplier", Text.Trim}})
     in
         #"Trimmed Text"

    Edit the second query according to this formula:

    let
        Source = Folder.Files("C:\Users\amendels\Desktop\MI Data Warehouse\Material Type"),
        #"Merged Columns" = Table.CombineColumns(Source,{"Folder Path", "Name"},Combiner.CombineTextByDelimiter("", QuoteStyle.None),"Merged"),
        #"Renamed Columns" = Table.RenameColumns(#"Merged Columns",{{"Merged", "path"}}),
        #"Removed Columns" = Table.RemoveColumns(#"Renamed Columns",{"Content", "Extension", "Date accessed", "Date modified", "Date created", "Attributes"}),
        #"Added Custom" = Table.AddColumn(#"Removed Columns", "Custom", each getWorkbook([path])),
        #"Expand Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"MaterialTypeID", "Material Type", "Family", "Extended Family", "Supplier"}, {"MaterialTypeID", "Material Type", "Family", "Extended Family", "Supplier"}),
        #"Removed Columns1" = Table.RemoveColumns(#"Expand Custom",{"path"})
    in
        #"Removed Columns1"

    • Marked as answer by עמיתת Monday, February 16, 2015 10:24 PM
    Monday, February 16, 2015 10:16 PM

All replies

  • 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.

    i.e.: 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 all and consolidate to one table in my power pivot model with the desired formation.

    Appreciate any help!


    עמית

    Monday, February 16, 2015 12:15 PM
  • What you want to do is to parse the supplier text into a list and then expand the list. The first of these can't easily be done via the UI, I think, so you'd need to go to the "Advanced Editor" and edit the query directly. Here's some sample code that worked for my test data.

    let
        Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
        ToList = Table.TransformColumns(Source, {"Supplier", Splitter.SplitTextByDelimiter("*")}),
        #"Expand Supplier" = Table.ExpandListColumn(ToList, "Supplier")
    in
        #"Expand Supplier"

    Monday, February 16, 2015 5:09 PM
  • Hi and thx for replying!

    It doesn't work for me... :(


    Do I need the source table to be in a "Table" (Ctrl T)? 

    Do I need to add something to the code? 

    Did you start the code from the source file or from another file to which you tried to import the new table?


    עמית

    Monday, February 16, 2015 5:49 PM
  • This is how I extract the data (without the required manipulation):

    let
        Source = Folder.Files("C:\Users\am\Desktop\Data Warehouse\Material Type"),
        #"Added Custom" = Table.AddColumn(Source, "GetMIData", each Excel.Workbook([Content])),
        #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Content", "Extension", "Attributes", "Folder Path"}),
        #"Expand GetMIData" = Table.ExpandTableColumn(#"Removed Columns", "GetMIData", {"Name", "Data", "Item", "Kind"}, {"GetMIData.Name", "GetMIData.Data", "GetMIData.Item", "GetMIData.Kind"}),
        #"Added Custom1" = Table.AddColumn(#"Expand GetMIData", "NoHeaders", each Table.PromoteHeaders([GetMIData.Data])),
        #"Removed Columns1" = Table.RemoveColumns(#"Added Custom1",{"GetMIData.Name", "GetMIData.Data", "GetMIData.Item", "GetMIData.Kind"}),
        #"Expand NoHeaders" = Table.ExpandTableColumn(#"Removed Columns1", "NoHeaders", {"MaterialTypeID", "Material Type", "Family", "Extended Family", "Supplier"}, {"MaterialTypeID", "Material Type", "Family", "Extended Family", "Supplier"})
    in
        #"Expand NoHeaders"

    Not sure where I need to add your suggested line codes...


    עמית

    Monday, February 16, 2015 5:55 PM
  • I don't know if that is the way but I tried to edit the code myself:

    let
        Source = Folder.Files("C:\Users\am\Desktop\Data Warehouse\Material Type"),
        #"Added Custom" = Table.AddColumn(Source, "GetMIData", each Excel.Workbook([Content])),
        #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Content", "Extension", "Attributes", "Folder Path"}),
        #"Expand GetMIData" = Table.ExpandTableColumn(#"Removed Columns", "GetMIData", {"Name", "Data", "Item", "Kind"}, {"GetMIData.Name", "GetMIData.Data", "GetMIData.Item", "GetMIData.Kind"}),
        #"Added Custom1" = Table.AddColumn(#"Expand GetMIData", "NoHeaders", each Table.PromoteHeaders([GetMIData.Data])),
        #"Removed Columns1" = Table.RemoveColumns(#"Added Custom1",{"GetMIData.Name", "GetMIData.Data", "GetMIData.Item", "GetMIData.Kind"}),
        #"Expand NoHeaders" = Table.ExpandTableColumn(#"Removed Columns1", "NoHeaders", {"MaterialTypeID", "Material Type", "Family", "Extended Family", "Supplier"}, {"MaterialTypeID", "Material Type", "Family", "Extended Family", "Supplier"}),
    ToList = Table.TransformColumns(Source, {"Supplier", Splitter.SplitTextByDelimiter("*")}),
        #"Expand Supplier" = Table.ExpandListColumn(ToList, "Supplier")
    in
        #"Expand Supplier"

    and got this error:


    עמית

    Monday, February 16, 2015 6:01 PM
  • Assuming all your workbooks have the data in Sheet1 - You should take Curt's code and change it to a function query (below) and save it as "getWorkbook"

    (path) =>

    let Source = Excel.Workbook(File.Contents(path)),
        Source2 = Source{[Item="Sheet1",Kind="Sheet"]}[Data], ToList = Table.TransformColumns(Source2, {"Supplier", Splitter.SplitTextByDelimiter("*")}), #"Expand Supplier" = Table.ExpandListColumn(ToList, "Supplier") in #"Expand Supplier"

    Now you should create a new query with this code. I can also send you step by step instructions how to create it in the UI by using From Folder. Below change [folder path here] to your actual folder path.

    let
        Source = Folder.Files([folder path here]),
        #"Removed Other Columns" = Table.SelectColumns(Source,{"Name", "Folder Path"}),
        #"Merged Columns" = Table.CombineColumns(#"Removed Other Columns",{"Folder Path", "Name"},Combiner.CombineTextByDelimiter("", QuoteStyle.None),"path"),
        #"Added Custom" = Table.AddColumn(#"Merged Columns", "Custom", each getWorkbook([path])),
        #"Expand Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"Column1", "Column2", "Column3", "Column4", "Column5"}, {"Column1", "Column2", "Column3", "Column4", "Column5"}),
        #"Removed Columns" = Table.RemoveColumns(#"Expand Custom",{"path"}),
        #"Removed Duplicates" = Table.Distinct(#"Removed Columns", {"Column1"}),
        #"First Row as Header" = Table.PromoteHeaders(#"Removed Duplicates")
    in
        #"First Row as Header"





    Monday, February 16, 2015 6:08 PM
  • Hi Gil, 

    This is the second time you're assisting me in this forum. I appreciate it a lot.

    What are the steps to create a Function Query, as you suggested? 



    עמית

    Monday, February 16, 2015 6:15 PM
  • We want happy customers :)

    To create the function query follow these steps:

    1- Copy the query above (the one that starts with (path) =>

    2- Open blank query from Power Query ribbon -> From Other Sources -> Blank Query

    3- In the Editor go to View tab and click Advanced Editor.

    4- Paste the formula in the text box.

    5- Close the editor and keep changes.

    6- Rename the new function query "getWorkbook". You can do it from the Workbook Queries pane (or inside the editor at the previous step).

    Now you have the function query.

    You can follow the steps above to create the second query. The second query was created by a mix of UI and light formula editing. Follow the next section if you want to learn how it was created.

    You can create it by using "From Folder", then combine the path and filename into a single column. Next, add custom column and call the function query from it with the combined column of path+file name as the parameter. Next, expand the table results, remove duplicate rows (you will have multiple rows with the column names), and promote the first row as headers. Now you are ready to go.


    • Proposed as answer by Gil RavivMVP Monday, February 16, 2015 7:04 PM
    • Edited by Gil RavivMVP Monday, February 16, 2015 7:16 PM
    Monday, February 16, 2015 7:04 PM
  • When pasting the first lines of code i get:

    Expression.SyntaxError: Token Comma expected.


    עמית

    Monday, February 16, 2015 7:12 PM
  • Sorry. Try now. I edited the formula. Whenever you see such errors it means we forgot to add a comma at the end of a step/line.
    Monday, February 16, 2015 7:20 PM
  • I realized the mistake earlier so I continued... 

    FYI- when I closed the first Function Query it was shown as disabled in the "Show Pane" Menu. 

    Continuing to the second Function Query resulted in another error


    עמית

    Monday, February 16, 2015 7:25 PM
  • The disabled function is the designed experience. You can use other queries to access that function.

    The error you see is because you need to use double quotes and not brackets for the input string that represents your folder path.

    Monday, February 16, 2015 7:33 PM

  • It looks as if anything can go wrong- it will :)

    <gs class="GINGER_SOFTWARE_mark" ginger_software_uiphraseguid="bf577f31-b5b2-4e33-8295-b503a9a6b597" id="31c7f26a-d1ac-4302-ab8a-934ec6378fda">let</gs>
        Source = Folder.Files("C:\Users\amendels\Desktop\MI Data Warehouse\Material Type"),
        #"Removed Other Columns" = Table<gs class="GINGER_SOFTWARE_mark" ginger_software_uiphraseguid="d16c857e-92ce-49fd-b50d-234fb6306a78" id="00650cfc-ee61-400b-bce8-93f3d08e806c">.</gs><gs class="GINGER_SOFTWARE_mark" ginger_software_uiphraseguid="d16c857e-92ce-49fd-b50d-234fb6306a78" id="05a88f4a-cc9a-412e-839a-a3eee9793fc0">SelectColumns</gs><gs class="GINGER_SOFTWARE_mark" ginger_software_uiphraseguid="d16c857e-92ce-49fd-b50d-234fb6306a78" id="ec79ede6-2a0b-449d-8733-e8b743d0ca0b">(</gs>Source<gs class="GINGER_SOFTWARE_mark" ginger_software_uiphraseguid="d16c857e-92ce-49fd-b50d-234fb6306a78" id="833506c2-9e6b-4877-85db-e59e0106a415">,</gs>{"Name", "Folder Path"}),
        #"Merged Columns" = Table<gs class="GINGER_SOFTWARE_mark" ginger_software_uiphraseguid="37c10c24-bce5-4d43-835c-46ed9cf2d5ed" id="19ec4b4d-02d5-4779-b1d8-70e054a55169">.</gs><gs class="GINGER_SOFTWARE_mark" ginger_software_uiphraseguid="37c10c24-bce5-4d43-835c-46ed9cf2d5ed" id="75be0e50-829a-44f8-bce0-c5c650ef25b1">CombineColumns</gs><gs class="GINGER_SOFTWARE_mark" ginger_software_uiphraseguid="37c10c24-bce5-4d43-835c-46ed9cf2d5ed" id="4c6d962d-3323-4a2d-af5d-deb764c514cd">(</gs>#"Removed Other Columns"<gs class="GINGER_SOFTWARE_mark" ginger_software_uiphraseguid="37c10c24-bce5-4d43-835c-46ed9cf2d5ed" id="3946184a-730d-40e8-91ea-fd2d81275202">,</gs>{"Folder Path", "Name"}<gs class="GINGER_SOFTWARE_mark" ginger_software_uiphraseguid="37c10c24-bce5-4d43-835c-46ed9cf2d5ed" id="c6268911-7d6e-43c5-8814-bad897f5a05a">,</gs>Combiner<gs class="GINGER_SOFTWARE_mark" ginger_software_uiphraseguid="37c10c24-bce5-4d43-835c-46ed9cf2d5ed" id="dccb45ac-42e5-4a27-a150-c7d78d286814">.</gs>CombineTextByDelimiter<gs class="GINGER_SOFTWARE_mark" ginger_software_uiphraseguid="37c10c24-bce5-4d43-835c-46ed9cf2d5ed" id="edb72e5c-842a-4887-be04-b12fea521766">(</gs>"", QuoteStyle<gs class="GINGER_SOFTWARE_mark" ginger_software_uiphraseguid="37c10c24-bce5-4d43-835c-46ed9cf2d5ed" id="0ab10849-2fe6-4841-a97a-84732c546fc2">.</gs>None),"path"),
        #"Added Custom" = Table<gs class="GINGER_SOFTWARE_mark" ginger_software_uiphraseguid="230c9450-a9fa-4694-ba10-df8a0eefbcfa" id="f66eebe2-c524-4cee-91df-e08bd9441bd0">.</gs>AddColumn<gs class="GINGER_SOFTWARE_mark" ginger_software_uiphraseguid="230c9450-a9fa-4694-ba10-df8a0eefbcfa" id="2661e215-822d-4461-ae1a-38ddaffdcfc2">(</gs>#"Merged Columns", "Custom", each <gs class="GINGER_SOFTWARE_mark" ginger_software_uiphraseguid="230c9450-a9fa-4694-ba10-df8a0eefbcfa" id="c471a6f8-de61-44ec-a4eb-4fe5879b86b3">getWorkbook</gs><gs class="GINGER_SOFTWARE_mark" ginger_software_uiphraseguid="230c9450-a9fa-4694-ba10-df8a0eefbcfa" id="beb17741-a1bd-45ee-8770-667b6a429b31">(</gs>[path])),
        #"Expand Custom" = Table<gs class="GINGER_SOFTWARE_mark" ginger_software_uiphraseguid="7656e752-6806-471e-aac1-aca2acc34672" id="6ab7b6f5-e01e-4969-8f22-ec937a2e8f68">.</gs>ExpandTableColumn<gs class="GINGER_SOFTWARE_mark" ginger_software_uiphraseguid="7656e752-6806-471e-aac1-aca2acc34672" id="064c2ab1-0322-4b2d-9734-86b2fca4d3dd">(</gs>#"Added Custom", "Custom", {"Column1", "Column2", "Column3", "Column4", "Column5", "Column6", "Column7", "Column8", "Column9", "Column10", "Column11", "Column12"}, {"Column1", "Column2", "Column3", "Column4", "Column5", "Column6", "Column7", "Column8", "Column9", "Column10", "Column11", "Column12"}),
        #"Removed Columns" = Table<gs class="GINGER_SOFTWARE_mark" ginger_software_uiphraseguid="73ce11cc-0be6-423c-9cd7-996ab0d86c3e" id="875308f8-4505-42bc-bcc4-be136c0d8b99">.</gs><gs class="GINGER_SOFTWARE_mark" ginger_software_uiphraseguid="73ce11cc-0be6-423c-9cd7-996ab0d86c3e" id="43a91980-9dd1-40da-84d4-4c2700db63ed">RemoveColumns</gs><gs class="GINGER_SOFTWARE_mark" ginger_software_uiphraseguid="73ce11cc-0be6-423c-9cd7-996ab0d86c3e" id="d8ecfee3-de63-4dd9-a916-ebdc7ab155f3">(</gs>#"Expand Custom"<gs class="GINGER_SOFTWARE_mark" ginger_software_uiphraseguid="73ce11cc-0be6-423c-9cd7-996ab0d86c3e" id="70479dcb-a6a3-44ac-a329-476bd6c2f216">,</gs>{"path"}),
        #"Removed Duplicates" = Table<gs class="GINGER_SOFTWARE_mark" ginger_software_uiphraseguid="42c1a334-cff4-432d-8d41-237fbb6cfa2b" id="4efdfd6c-b953-46bd-a673-d9f93ed4fb64">.</gs>Distinct<gs class="GINGER_SOFTWARE_mark" ginger_software_uiphraseguid="42c1a334-cff4-432d-8d41-237fbb6cfa2b" id="4108b4fd-3cbb-44ff-8f51-3e2f51e9b1e5">(</gs>#"Removed Columns", {"Column1"}),
        #"First Row as Header" = Table<gs class="GINGER_SOFTWARE_mark" ginger_software_uiphraseguid="e97a3de6-d2de-4872-b8f1-7b26dbacacae" id="9b321e4e-0fbb-40be-9953-5918b7b2c363">.</gs>PromoteHeaders<gs class="GINGER_SOFTWARE_mark" ginger_software_uiphraseguid="e97a3de6-d2de-4872-b8f1-7b26dbacacae" id="c566d7bd-ca67-457e-9698-dcaf9cf7ef90">(</gs>#"Removed Duplicates")
    <gs class="GINGER_SOFTWARE_mark" ginger_software_uiphraseguid="7344bfb8-a967-407a-8229-646035c0264c" id="2719b7a9-2b95-4862-8054-67f5da25353a">in</gs>
        #"First Row as Header"
    • Edited by עמיתת Monday, February 16, 2015 7:49 PM
    Monday, February 16, 2015 7:48 PM
  • let
        Source = Folder.Files("C:\Users\amendels\Desktop\MI Data Warehouse\Material Type"),
        #"Removed Other Columns" = Table.SelectColumns(Source,{"Name", "Folder Path"}),
        #"Merged Columns" = Table.CombineColumns(#"Removed Other Columns",{"Folder Path", "Name"},Combiner.CombineTextByDelimiter("", QuoteStyle.None),"path"),
        #"Added Custom" = Table.AddColumn(#"Merged Columns", "Custom", each getWorkbook([path])),
        #"Expand Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"Column1", "Column2", "Column3", "Column4", "Column5", "Column6", "Column7", "Column8", "Column9", "Column10", "Column11", "Column12"}, {"Column1", "Column2", "Column3", "Column4", "Column5", "Column6", "Column7", "Column8", "Column9", "Column10", "Column11", "Column12"}),
        #"Removed Columns" = Table.RemoveColumns(#"Expand Custom",{"path"}),
        #"Removed Duplicates" = Table.Distinct(#"Removed Columns", {"Column1"}),
        #"First Row as Header" = Table.PromoteHeaders(#"Removed Duplicates")
    in
        #"First Row as Header"

    עמית

    Monday, February 16, 2015 7:49 PM
  • The error above states that you don't have Supplier column name. Can you check if you do?
    Monday, February 16, 2015 8:16 PM

  • עמית

    Monday, February 16, 2015 8:20 PM
  • Try [Column5] instead of [Supplier]. If it doesn't help, try to create tables (ctrl+T) and use first row as headers.

    In addition, in the formula above there is reference to 12 columns. You can edit it to 5 columns. I will edit the original formula above.

    Monday, February 16, 2015 8:31 PM
  • Where did you use/see [Supplier] in the code?

    I couldn't find 


    עמית

    Monday, February 16, 2015 8:42 PM
  • @Gil Raviv

    This is what I did:

    Function 1:

    (path) =>
    let
        Source = Excel.Workbook(File.Contents(path)),
        Source2 = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
        ToList = Table.TransformColumns(Source2, {"Column5", Splitter.SplitTextByDelimiter("*")}),
         #"Expand Supplier" = Table.ExpandListColumn(ToList, "Column5")
     in
         #"Expand Supplier"

    Function 2:

    let
        Source = Folder.Files("C:\Users\amendels\Desktop\MI Data Warehouse\Material Type"),
        #"Removed Other Columns" = Table.SelectColumns(Source,{"Name", "Folder Path"}),
        #"Merged Columns" = Table.CombineColumns(#"Removed Other Columns",{"Folder Path", "Name"},Combiner.CombineTextByDelimiter("", QuoteStyle.None),"path"),
        #"Added Custom" = Table.AddColumn(#"Merged Columns", "Custom", each getWorkbook([path])),
        #"Expand Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"Column1", "Column2", "Column3", "Column4", "Column5", "Column6", "Column7", "Column8", "Column9", "Column10", "Column11", "Column12"}, {"Column1", "Column2", "Column3", "Column4", "Column5", "Column6", "Column7", "Column8", "Column9", "Column10", "Column11", "Column12"}),
        #"Removed Columns" = Table.RemoveColumns(#"Expand Custom",{"path"}),
        #"Removed Duplicates" = Table.Distinct(#"Removed Columns", {"Column1"}),
        #"First Row as Header" = Table.PromoteHeaders(#"Removed Duplicates")
    in
        #"First Row as Header"

    It managed to import the data, HOWEVER, it took just one line with the first supplier name, and all the rest just ignored (didn't repeat the row with the other names),


    עמית


    • Edited by עמיתת Monday, February 16, 2015 9:40 PM
    Monday, February 16, 2015 9:38 PM
  • Sorry for the long iterations. I used different samples, which yielded wrong results.

    The following formulas should work now.

    Edit the formula code of getWorkbook according to this section:

    (path) =>
    
    let
        Source = Excel.Workbook(File.Contents(path)),
        Source2 = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
        ToList = Table.TransformColumns(Source2, {"Column5", Splitter.SplitTextByDelimiter("*")}),
        #"Expand Column" = Table.ExpandListColumn(ToList, "Column5"),
        #"First Row as Header" = Table.PromoteHeaders(#"Expand Column"),
        #"Trimmed Text" = Table.TransformColumns(#"First Row as Header",{{"Supplier", Text.Trim}})
     in
         #"Trimmed Text"

    Edit the second query according to this formula:

    let
        Source = Folder.Files("C:\Users\amendels\Desktop\MI Data Warehouse\Material Type"),
        #"Merged Columns" = Table.CombineColumns(Source,{"Folder Path", "Name"},Combiner.CombineTextByDelimiter("", QuoteStyle.None),"Merged"),
        #"Renamed Columns" = Table.RenameColumns(#"Merged Columns",{{"Merged", "path"}}),
        #"Removed Columns" = Table.RemoveColumns(#"Renamed Columns",{"Content", "Extension", "Date accessed", "Date modified", "Date created", "Attributes"}),
        #"Added Custom" = Table.AddColumn(#"Removed Columns", "Custom", each getWorkbook([path])),
        #"Expand Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"MaterialTypeID", "Material Type", "Family", "Extended Family", "Supplier"}, {"MaterialTypeID", "Material Type", "Family", "Extended Family", "Supplier"}),
        #"Removed Columns1" = Table.RemoveColumns(#"Expand Custom",{"path"})
    in
        #"Removed Columns1"

    • Marked as answer by עמיתת Monday, February 16, 2015 10:24 PM
    Monday, February 16, 2015 10:16 PM
  • Amazing :)

    Thx a lot!


    עמית

    Monday, February 16, 2015 10:24 PM