none
Gradual files combining/processing RRS feed

  • Question

  • Hello, I have a list of same simple structure files and I need to transform each a bit and combine them all.

    File  |  Value
    1     |  1
    1     |  2
    1     |  3
    
    
    File  |  Value
    2     |  3
    2     |  4
    2     |  5
    
    
    File  |  Value
    3     |  6
    3     |  7
    3     |  8


    The trick is firstly I need to combine first 2 files, make some calculations and only then repeat this proccess again but this time it would be combined 1+2 file with the 3rd one. Like this:

    File 1+2 |  Value   |  New value 
    1            |  1          |  11 
    1            |  2          |  22 
    1            |  3          |  33 
    2            |  3          |  44 
    2            |  4          |  55 
    2            |  5          |  66 
    
    
    File  |  Value
    3     |  6
    3     |  7
    3     |  8

    Hopefully I managed to describe it well.

    Is that possible in PQ and how can you name such process, where should I dig?

    Thursday, October 11, 2018 3:28 PM

Answers

All replies

  • It sounds like you might want to look into the Merge and Append commands.

    Ehren

    Thursday, October 11, 2018 8:27 PM
    Owner
  • Thanks, Ehren! Let me clarify my situation, in my example with 3 files it is quick and easy but I have hundreds of files, I know the merge and append commands but I need some dynamic technique for that. 

    We can make function and use it to the list of files: where for each row this function will be implemented but that will be simultaniously.

    I need to make it gradualy: for example 1 dynamic query that this function will be referencing every time before next row (file), in each next row use an updated query(table) that is used in function. Something like this:

    Step1: file 1 + file 2

    Step2: file1&2 + file3

    Step3; file123 + file4

    Sorry for such description but I don't know how to name what I am searching for (loop?), hope this will help. 

    Friday, October 12, 2018 9:54 AM
  • What's the structure/format of the files? Have you tried using the Folder data source? That should do a bunch of the heavy lifting for you and allow you to apply a set of common transformation steps to all the files.

    Ehren

    Monday, October 15, 2018 6:29 PM
    Owner
  • Check out this blogpost: https://www.thebiccountant.com/2017/09/26/recursion-m-beginners/

    It also gives links to List.Generate, which usually performs this task faster.


    Imke Feldmann - MVP Data Platform (PowerBI) - http://www.TheBIccountant.com 

    Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to vote it as helpful - Thanks!

    Tuesday, October 16, 2018 7:47 AM
    Moderator