Power Query - Loop through unknown number of Tables and append them all - solved

Anonymous
2018-07-17T09:29:18+00:00

Hello,

I have an Excel spreadsheet with a lot of columns but I want to save it in csv format with line breaks to put certain columns on different lines.   This is because I can upload different record types into my database but each record type needs its own line in csv with a header type as the first field on that line.

eg this in Excel:

col1   col2   col3   col4   col5   col6   col7   col8   col9   col10

01        b        c        d       e        02         g       h        03         j

01        r         s         t       u        02         v       w        03         x

becomes this in csv:

01, b, c, d, e

02, g, h

03, j

01, r, s, t, u

02, v, w

03, x              

Is this possible?  Easy?  

thanks

Louise

<The thread has been moved to the correct category by forum moderator>

Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments
Answer accepted by question author
Lz365 38,201 Reputation points Volunteer Moderator
2018-08-01T12:23:56+00:00

This is a very simplified version of this thread. We have an Input Table that looks like:

where each Group of colored columns needs to be transformed in a separate Table (so 3 in this case) and at the end we want the 3 Tables to be Appended (Combined according to Power Query M language)

Challenge

The number of Groups may vary (i.e. from 1 to say 10) from time to time. So we need a query that is flexible enough to handle this variation

Note

In the above example the 1st Group has 3 columns, the 2nd has 4 and the last has 5. To properly Append/Combine Tables they all must have the same number of columns. That aspect of the problem is not detailed in the below Power Query M snipet.

How To

Power Quey M language doesn't offer an easy way to loop. This example uses the List.Generate function to do that the appropriate number of times to build each Table and then Append/Combine them

let

    ...

    GroupsList = Record.ToList(Table.FirstN(Source, 1){0}),

    NbGroups = List.Count(List.Distinct(GroupsList)),

    // Loop (actually NbGroups of times) to create a Table for each "Group"

    // At the end we get a List that contains Table(s)

    The next Step will Append/Combine all Table(s)

    TablesToAppend = List.Generate(

         ()=> [i = -1, j = 0, OutputTable = #table({"Col","Index","Index2"},{})],

            each [i] < NbGroups,

                each

                    [

                        j = [j] +1,

                        ...,              // step1 to build the OutputTable

                        ...,              // step2 to build the OutputTable

                        TableIdx = ...,   // step3 to build the OutputTable

                        OutputTable = Table.AddIndexColumn(TableIdx, "Index2", j, 1),

                        i = [i] +1

                    ],

                each [OutputTable]

                                  ),

    // Append all tables from above List

    TablesAppended = Table.Combine(List.Transform(TablesToAppend, each (_))),

    ...

in

    ...

Was this answer helpful?

2 people found this answer helpful.
0 comments

59 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Lz365 38,201 Reputation points Volunteer Moderator
    2018-07-30T09:38:30+00:00

    Good Morning Louise,

    Before I try to answer your questions please clarify what you call "record type". Is this (based on the sample used so far): 00EMPLOYEE, 10PERDET and 35EMPBASIC?

    Was this answer helpful?

    0 comments
  2. Anonymous
    2018-07-30T08:55:08+00:00

    Morning,

    OK I've found the Power Query.  

    The way the API works on my system is that the record type indicator has to begin each row of data on the csv output.  There are about 23 record types that we may want to load data for and they have various numbers of columns per record type.  We only need to load blank columns up to the last column being populated, eg if there are 30 columns on one record type but the last one we populate is number 20 we won't put in the last 10 columns. 

    I see in the code there is a section of code headed 1st group, 2nd group, 3rd group so I'm presuming there will need to be one of these for each record type? I can see some number references increase sequentially (DistinctCol{0}, DistinctCol{1}, etc) so I presume these need increasing also? 

    I see reference to Table 2, 3, which I saw in the list of steps.  Are these like sub-routines?  (although if I click on advanced editor it only goes to the one piece of code, but if I click on the step it shows the relevant data in the main window).

    thanks

    Was this answer helpful?

    0 comments
  3. Lz365 38,201 Reputation points Volunteer Moderator
    2018-07-28T13:42:46+00:00

    Hi Louise

    Breaking news, I've just got a laptop and it works on there!!  :0)

    Very good news :-))))

    I'll also need to add columns and at least one additional record type

    The query currently takes into account 3 group of columns, independently of the number of columns per group, as long as there are defined (so with the same name) in row 3 of the Input table. To make it clear and taking your current input sheet as example: there is a 1st group of 6 columns "named" 00EMPLOYEE in row 3 (actually the 1st data row of the table). If you add in extra column, let's say G, and "name" it  00EMPLOYEE in that row 3 the query will pick it up with no change. Same goes for the other groups. Now, if you plan to add a "group", so a 4th one, then the query needs to updated.

    I you can quickly (on vacation by next Friday) explain me precisely what you mean with adding columns I might be able to guide you before leaving

    I can read SQL enough to modify something

    I'm afraid this won't really help as Power Query "language" known as M has nothing to do with SQL, VBA... BTW. To access the query steps and see the code:

    • Click somewhere in TBL_OUTPUT (QRY OUTPUT sheet)
    • At the very top of the Excel window click Query Tools
    • On the left of the ribbon click Edit - This opens the Power Query editor

    On the right side you see the steps (you'll find them in the code). On the Home tab click Advanced Editor to see the M code. Don't spend time trying to understand the FUNCTION at the top it's useless

    Was this answer helpful?

    0 comments
  4. Anonymous
    2018-07-27T13:38:32+00:00

    Hello,

    I tried this at home and it worked!! 

    I did the same screen grabs as before (https://www.dropbox.com/s/ne6yczhngtwi7cs/Home%20test%20log.docx?dl=0) and I thought the main difference was that at home the message is about Protected View but I tried again at work this morning and it did open like that but didn't make any difference.   

    Breaking news, I've just got a laptop and it works on there!!  :0)  There is obviously something on my desktop that is interfering with the macro.  Great result!!

    So now I can add rows but I'll also need to add columns and at least one additional record type, that is the bit that adds in the line break.  Is that something I'll be able to do?  As I can read SQL enough to modify something like this (copy and add a new row or column) I was anticipating being able to that here and not rely on you for every minor detail when you've already spent a lot of time on this (although I hope you've enjoyed it and possibly learnt something?!  ;0) )

    Thanks

    Was this answer helpful?

    0 comments