Forum Discussion

oxologic's avatar
oxologic
Frequent Visitor
6 years ago
Solved

Merging Files with Fixed Set of Columns and Varied Date Columns (for unpivot)

Hey everyone, we have a scenario where we are trying to achieve a forecast of the products from different suppliers (for ease of explanation but would fit in most scenarios too). Therefore, we have C...
Need Help
  • Mariusz's avatar
    Mariusz
    6 years ago

    Hi oxologic 

     

    You can use Get data from SharePoint folder

    Then filter the Folder Path to select only the folder path with all the files in question.

     

    Create a Blank Query and paste the below script into Advanced Editor to create Function and rename it to "Function".

    ( content as binary ) => let 
    binaryToTable = Excel.Workbook( content ),
    getData  = binaryToTable{ [Item="Table1", Kind="Table"] }[Data],
    unpivot = Table.UnpivotOtherColumns( getData, {"Supplier", "Product"}, "Date", "Value" )
in
    unpivot

     When in the table with a list of files, go to Add Column ribbon > Invoke Kustom Function and follow the below.

     

    Now you can Expand all the columns from all files.

     

    Best Regards,
    Mariusz

    If this post helps, then please consider Accepting it as the solution.

     

Mariusz's avatar
Mariusz
Icon for Community Champion rankCommunity Champion
6 years ago

Hi oxologic 

 

Can you Provide samples for these files?

 

 

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.
oxologic's avatar
oxologic
Frequent Visitor
6 years ago

Mariusz , hi i've attached the sample data files through wetransfer. I thought there was a way to attach the files previously, but couldn't find it. 

 

https://we.tl/t-wqa1Av1Hdn

 

  • Mariusz's avatar
    Mariusz
    Icon for Community Champion rankCommunity Champion
    6 years ago

    Hi oxologic 

     

    You can use Get data from SharePoint folder

    Then filter the Folder Path to select only the folder path with all the files in question.

     

    Create a Blank Query and paste the below script into Advanced Editor to create Function and rename it to "Function".

    ( content as binary ) => let 
    binaryToTable = Excel.Workbook( content ),
    getData  = binaryToTable{ [Item="Table1", Kind="Table"] }[Data],
    unpivot = Table.UnpivotOtherColumns( getData, {"Supplier", "Product"}, "Date", "Value" )
in
    unpivot

     When in the table with a list of files, go to Add Column ribbon > Invoke Kustom Function and follow the below.

     

    Now you can Expand all the columns from all files.

     

    Best Regards,
    Mariusz

    If this post helps, then please consider Accepting it as the solution.