locked
Power Query - Loop thru iterative file update process RRS feed

  • Question

  • I would like to find a way to efficiently use a loop to iterate thru a file update process. I'm stuck on writing the loop.

    let // Identify the Initial File location stored as a variable table value on the "Dir" tab with the "CurrentMonthFiles" table cmf = Excel.CurrentWorkbook(){[Name="CurrentMonthFiles"]}[Content], // Choose the first file in the list - PQ uses "0" as the starting number CMFval = cmf{0}[DirAndFile], // Import the Initial File by using the function "fnFileFormat" to import the Initial File called from a variable table in the step above InitFile = fnFileFormat(CMFval), // Identify the maximum number of files table source to iterate thru after the Initial File as a variable table value on the "Dir" tab with the "cmfFileCount" table cmfCount = Excel.CurrentWorkbook(){[Name="cmfFileCount"]}[Content], // Setup the iterative variable value as an integer and the end point as an integer Declare i integer, Declare iAF as integer, // Pull the end point as a variable table value on the "Dir" tab with the "cmfFileCount" table iAF = cmfCount{0}[PQcmfCnt], // Define length of loop For i=1 to iAF, "CMFval & i" = cmf{i}[DirAndFile], "DR & i" = fnDelete("CMFval & i"), "IFDR & i" = try Table.NestedJoin(InitFile,{"X"},"DR & i",{"X"},"NewColumn",JoinKind.LeftAnti) otherwise InitFile, "ARval & i" = cmf{i}[DirAndFile], "AR & i" = fnAddReplace("ARval & i"), "IF & i + 1" = try Table.Combine({"IFDR & i","AR & i"}) otherwise "IFDR & i"

    next i in "IF & i + 1"


    A loop would be preferable to this chain of commands. They work just fine but they aren't pretty.

    let

       

        // Import and Format of the Initial File

        

        cmf = Excel.CurrentWorkbook(){[Name="CurrentMonthFiles"]}[Content],

     

        CMFval = cmf{0}[DirAndFile],

        InitFile = fnFileFormat(CMFval),

        // File Set 2

        CMFval1 = cmf{1}[DirAndFile],

       

        DR1 = fnDelete(CMFval1),

        IFDR1 = try Table.NestedJoin(InitFile,{"X"},DR1,{"X"},"NewColumn",JoinKind.LeftAnti) otherwise InitFile,

        ARval1 = cmf{1}[DirAndFile],

        AR1 = fnAddReplace(ARval1),

        IF2 = try Table.Combine({IFDR1,AR1}) otherwise IFDR1,

        // File Set 3

        CMFval2 = cmf{2}[DirAndFile],

       

        DR2 = fnDelete(CMFval2),

        IFDR2 = try Table.NestedJoin(IF2,{"X"},DR2,{"X"},"NewColumn",JoinKind.LeftAnti) otherwise IF2,

        ARval2 = cmf{2}[DirAndFile],

        AR2 = fnAddReplace(ARval2),

        IF3 = try Table.Combine({IFDR2,AR2}) otherwise IFDR2,

        // File Set 4

        CMFval3 = cmf{3}[DirAndFile],

       

        DR3 = fnDelete(CMFval3),

        IFDR3 = try Table.NestedJoin(IF3,{"X"},DR3,{"X"},"NewColumn",JoinKind.LeftAnti) otherwise IF3,

        ARval3 = cmf{3}[DirAndFile],

        AR3 = fnAddReplace(ARval3),

        IF4 = try Table.Combine({IFDR3,AR3}) otherwise IFDR3,

        // File Set 5

        CMFval4 = cmf{4}[DirAndFile],

       

        DR4 = fnDelete(CMFval4),

        IFDR4 = try Table.NestedJoin(IF4,{"X"},DR4,{"X"},"NewColumn",JoinKind.LeftAnti) otherwise IF4,

        ARval4 = cmf{4}[DirAndFile],

        AR4 = fnAddReplace(ARval4),

        IF5 = try Table.Combine({IFDR4,AR4}) otherwise IFDR4,

        // File Set 6

        CMFval5 = cmf{5}[DirAndFile],

       

        DR5 = fnDelete(CMFval5),

        IFDR5 = try Table.NestedJoin(IF5,{"X"},DR5,{"X"},"NewColumn",JoinKind.LeftAnti) otherwise IF5,

        ARval5 = cmf{5}[DirAndFile],

        AR5 = fnAddReplace(ARval5),

        IF6 = try Table.Combine({IFDR5,AR5}) otherwise IFDR5,

        // File Set 7

        CMFval6 = cmf{6}[DirAndFile],

       

        DR6 = fnDelete(CMFval6),

        IFDR6 = try Table.NestedJoin(IF6,{"X"},DR6,{"X"},"NewColumn",JoinKind.LeftAnti) otherwise IF6,

        ARval6 = cmf{6}[DirAndFile],

        AR6 = fnAddReplace(ARval6),

        IF7 = try Table.Combine({IFDR6,AR6}) otherwise IFDR6,

        // File Set 8

        CMFval7 = cmf{7}[DirAndFile],

       

        DR7 = fnDelete(CMFval7),

        IFDR7 = try Table.NestedJoin(IF7,{"X"},DR7,{"X"},"NewColumn",JoinKind.LeftAnti) otherwise IF7,

        ARval7 = cmf{7}[DirAndFile],

        AR7 = fnAddReplace(ARval7),

        IF8 = try Table.Combine({IFDR7,AR7}) otherwise IFDR7,

        // File Set 9

        CMFval8 = cmf{8}[DirAndFile],

       

        DR8 = fnDelete(CMFval8),

        IFDR8 = try Table.NestedJoin(IF8,{"X"},DR8,{"X"},"NewColumn",JoinKind.LeftAnti) otherwise IF8,

        ARval8 = cmf{8}[DirAndFile],

        AR8 = fnAddReplace(ARval8),

        IF9 = try Table.Combine({IFDR8,AR8}) otherwise IFDR8,

        // File Set 10

        CMFval9 = cmf{9}[DirAndFile],

       

        DR9 = fnDelete(CMFval9),

        IFDR9 = try Table.NestedJoin(IF9,{"X"},DR9,{"X"},"NewColumn",JoinKind.LeftAnti) otherwise IF9,

        ARval9 = cmf{9}[DirAndFile],

        AR9 = fnAddReplace(ARval9),

        IF10 = try Table.Combine({IFDR9,AR9}) otherwise IFDR9,

        // File Set 11

        CMFval10 = cmf{10}[DirAndFile],

       

        DR10 = fnDelete(CMFval10),

        IFDR10 = try Table.NestedJoin(IF10,{"X"},DR10,{"X"},"NewColumn",JoinKind.LeftAnti) otherwise IF10,

        ARval10 = cmf{10}[DirAndFile],

        AR10 = fnAddReplace(ARval10),

        IF11 = try Table.Combine({IFDR10,AR10}) otherwise IFDR10,

        // File Set 12

        CMFval11 = cmf{11}[DirAndFile],

       

        DR11 = fnDelete(CMFval11),

        IFDR11 = try Table.NestedJoin(IF11,{"X"},DR11,{"X"},"NewColumn",JoinKind.LeftAnti) otherwise IF11,

        ARval11 = cmf{11}[DirAndFile],

        AR11 = fnAddReplace(ARval11),

        IF12 = try Table.Combine({IFDR11,AR11}) otherwise IFDR11,

        // File Set 13

        CMFval12 = cmf{12}[DirAndFile],

       

        DR12 = fnDelete(CMFval12),

        IFDR12 = try Table.NestedJoin(IF12,{"X"},DR12,{"X"},"NewColumn",JoinKind.LeftAnti) otherwise IF12,

        ARval12 = cmf{12}[DirAndFile],

        AR12 = fnAddReplace(ARval12),

        IF13 = try Table.Combine({IFDR12,AR12}) otherwise IFDR12,

        // File Set 14

        CMFval13 = cmf{13}[DirAndFile],

       

        DR13 = fnDelete(CMFval13),

        IFDR13 = try Table.NestedJoin(IF13,{"X"},DR13,{"X"},"NewColumn",JoinKind.LeftAnti) otherwise IF13,

        ARval13 = cmf{13}[DirAndFile],

        AR13 = fnAddReplace(ARval13),

        IF14 = try Table.Combine({IFDR13,AR13}) otherwise IFDR13,

        // File Set 15

        CMFval14 = cmf{14}[DirAndFile],

       

        DR14 = fnDelete(CMFval14),

        IFDR14 = try Table.NestedJoin(IF14,{"X"},DR14,{"X"},"NewColumn",JoinKind.LeftAnti) otherwise IF14,

        ARval14 = cmf{14}[DirAndFile],

        AR14 = fnAddReplace(ARval14),

        IF15 = try Table.Combine({IFDR14,AR14}) otherwise IFDR14,

        // File Set 16

        CMFval15 = cmf{15}[DirAndFile],

       

        DR15 = fnDelete(CMFval15),

        IFDR15 = try Table.NestedJoin(IF15,{"X"},DR15,{"X"},"NewColumn",JoinKind.LeftAnti) otherwise IF15,

        ARval15 = cmf{15}[DirAndFile],

        AR15 = fnAddReplace(ARval15),

        IF16 = try Table.Combine({IFDR15,AR15}) otherwise IFDR15,

        // File Set 17

        CMFval16 = cmf{16}[DirAndFile],

       

        DR16 = fnDelete(CMFval16),

        IFDR16 = try Table.NestedJoin(IF16,{"X"},DR16,{"X"},"NewColumn",JoinKind.LeftAnti) otherwise IF16,

        ARval16 = cmf{16}[DirAndFile],

        AR16 = fnAddReplace(ARval16),

        IF17 = try Table.Combine({IFDR16,AR16}) otherwise IFDR16,

        // File Set 18

        CMFval17 = cmf{17}[DirAndFile],

       

        DR17 = fnDelete(CMFval17),

        IFDR17 = try Table.NestedJoin(IF17,{"X"},DR17,{"X"},"NewColumn",JoinKind.LeftAnti) otherwise IF17,

        ARval17 = cmf{17}[DirAndFile],

        AR17 = fnAddReplace(ARval17),

        IF18 = try Table.Combine({IFDR17,AR17}) otherwise IFDR17,

        // File Set 19

        CMFval18 = cmf{18}[DirAndFile],

       

        DR18 = fnDelete(CMFval18),

        IFDR18 = try Table.NestedJoin(IF18,{"X"},DR18,{"X"},"NewColumn",JoinKind.LeftAnti) otherwise IF18,

        ARval18 = cmf{18}[DirAndFile],

        AR18 = fnAddReplace(ARval18),

        IF19 = try Table.Combine({IFDR18,AR18}) otherwise IFDR18,

        // File Set 20

        CMFval19 = cmf{19}[DirAndFile],

       

        DR19 = fnDelete(CMFval19),

        IFDR19 = try Table.NestedJoin(IF19,{"X"},DR19,{"X"},"NewColumn",JoinKind.LeftAnti) otherwise IF19,

        ARval19 = cmf{19}[DirAndFile],

        AR19 = fnAddReplace(ARval19),

        IF20 = try Table.Combine({IFDR19,AR19}) otherwise IFDR19,

        // File Set 21

        CMFval20 = cmf{20}[DirAndFile],

       

        DR20 = fnDelete(CMFval20),

        IFDR20 = try Table.NestedJoin(IF20,{"X"},DR20,{"X"},"NewColumn",JoinKind.LeftAnti) otherwise IF20,

        ARval20 = cmf{20}[DirAndFile],

        AR20 = fnAddReplace(ARval20),

        IF21 = try Table.Combine({IFDR20,AR20}) otherwise IFDR20,

        // File Set 22

        CMFval21 = cmf{21}[DirAndFile],

       

        DR21 = fnDelete(CMFval21),

        IFDR21 = try Table.NestedJoin(IF21,{"X"},DR21,{"X"},"NewColumn",JoinKind.LeftAnti) otherwise IF21,

        ARval21 = cmf{21}[DirAndFile],

        AR21 = fnAddReplace(ARval21),

        IF22 = try Table.Combine({IFDR21,AR21}) otherwise IFDR21,

        // File Set 23

        CMFval22 = cmf{22}[DirAndFile],

       

        DR22 = fnDelete(CMFval22),

        IFDR22 = try Table.NestedJoin(IF22,{"X"},DR22,{"X"},"NewColumn",JoinKind.LeftAnti) otherwise IF22,

        ARval22 = cmf{22}[DirAndFile],

        AR22 = fnAddReplace(ARval22),

        IF23 = try Table.Combine({IFDR22,AR22}) otherwise IFDR22

    in

        IF23


    The loop method is preferable because the number of files to process is itself a variable that I am trying to reference with:

       // Pull the end point as a variable table value on the "Dir" tab with the "cmfFileCount" table
    
        iAF = cmfCount{0}[PQcmfCnt],
    
        
        // Define length of loop
    
        For i=1 to iAF,

    Then it wouldn't process 24 steps if there were only 3 files.







    • Edited by bryancb Tuesday, February 2, 2016 6:46 PM remove column heading name
    Tuesday, February 2, 2016 4:34 PM

Answers

  • Have you considered getting the files in the folder into a table and apply a custom function to each row ?

    http://datapigtechnologies.com/blog/index.php/using-power-query-to-combine-data-from-multiple-excel-files-into-one-table/

    Tuesday, February 2, 2016 8:42 PM

All replies

  • let

     

        // Identify the Initial File location stored as a variable table value on the "Dir" tab

        // with the "CurrentMonthFiles" table

        cmf = Excel.CurrentWorkbook(){[Name="CurrentMonthFiles"]}[Content],

     

        // Choose the first file in the list - PQ uses "0" as the starting number 

        CMFval = cmf{0}[DirAndFile],

        // Import the Initial File by using the function "fnFileFormat" to import the Initial File called

        // from a variable table in the step above

        InitFile = fnFileFormat(CMFval),

        // Identify the total number of iterations “cmfCount” = a variable table value on the "Dir" tab with the

        //   "cmfFileCount" table . The value in the table is the count of the number of files to process which

        //     are counted from “cmf” above.

        //      “iAF” is the value of the count - 1 since PQ counts from zero. The count was 19 – 1 (InitFile) = 18

        //        Starting at 1 and processing to 18 performs all iterations

        cmfCount = Excel.CurrentWorkbook(){[Name="cmfFileCount"]}[Content],

        iAF = cmfCount{0}[PQcmfCnt],

        // Setup iteration

        Iteration = {   

    //    setting the counter to iterate thru the files, have to use cmf2 cannot re-use cmf

    //    setting the counter to begin iteration at 1

                     ()=> [Counter=1, cmf2 = Excel.CurrentWorkbook(){[Name="CurrentMonthFiles"]}[Content]],

    //    setting a dynamic hard stop for iterations at 18 via “iAF”

                     each [Counter] <= iAF,

    //    get busy with iterations for each pass thru

                     each [Counter=[Counter]+1,

    //    execute function (fnDelete) to identify deplete/replace records in update table

                                                 DR = fnDelete(cmf{[Counter]}[DirAndFile]),

    //    remove delete/replace records from InitFile

                                                 IFDR = Table.NestedJoin(InitFile,{"X"},DR,{"X"},"NewColumn",JoinKind.LeftAnti),

    //    execute function (fnAddReplace) to identify add/replace records in update table

                                                 AR = fnAddReplace(cmf{[Counter]}[DirAndFile]),

    //    combine updated tables to generate new masterfile

                                                 DB = Table.Combine({IFDR,AR})],

    //    pass the iteration procedures to each file to process           

                     each [cmf]

                    }

      in

    //    bask in the glow of awesomeness

    DB


    • Marked as answer by bryancb Tuesday, February 2, 2016 7:15 PM
    • Edited by bryancb Tuesday, February 2, 2016 7:16 PM spelling and column header
    • Unmarked as answer by bryancb Wednesday, February 3, 2016 6:57 PM
    Tuesday, February 2, 2016 7:14 PM
  • Have you considered getting the files in the folder into a table and apply a custom function to each row ?

    http://datapigtechnologies.com/blog/index.php/using-power-query-to-combine-data-from-multiple-excel-files-into-one-table/

    Tuesday, February 2, 2016 8:42 PM