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: Oldest
Most helpful Newest Oldest
  1. Anonymous
    2018-07-30T09:57:46+00:00

    Morning,

    That's correct.  I've scanned a couple of pages from the API guide to clarify (a page of contents then two pages from the 10PERDET record type - sorry the scanner played up so pages 2 and 3 are the wrong way round!).

    https://www.dropbox.com/s/l9x6pdel2ev8hoh/Import%20document.pdf?dl=0

    Was this answer helpful?

    0 comments
  2. Lz365 38,201 Reputation points Volunteer Moderator
    2018-07-30T11:31:09+00:00

    OK, 00EMPLOYEE, 10PERDET, 35EMPBASIC and the like = Record types and there could be up to 23 record types.

    Not sure where to start… When you submitted that request we were talking about 3 record types.

    With my limited Power Query skills I made it work doing almost the same thing for 3 "groups" as I called them. In other words I repeated almost the same Steps for each "group", adjusting the variables where required. I did this instead of looping through the "groups" as you would normally do with any programming language. Reasons are: 1) Power Query M language is procedural and doesn't offer a "natural" way of looping; 2) My limited skills. Experts in Power Query found a way of looping using the List.Generate function (really not an easy one until you master it) but I'm not at that level yet...

    So, what I'm trying to say is: making a single query that will handler a variable number of record types is probably doable but will require investments... Just to give you a sense of what a IF...THEN...ELSE block is all about have a look to this post

    Re. your specific questions/points:

    1) 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

    Except if I missed something so far the query already takes care of that (extra empty columns not reported in the Output)

    2) 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?

    Correct. But if you expect to make a single query you will have to implement some Conditional Code Branching (IF…THEN...ELSE)

    3) I can see some number references increase sequentially (DistinctCol{0}, DistinctCol{1}, etc) so I presume these need increasing also?

    Correct. This is "linked" to what I called "groups". DistinctCol is actually the List of unique column names - that is picked from the 1st record of data. List indexes start at zero. So for the 1st "group" we take DistinctCol{0}, 2nd "group" is DistinctCol{1}...

    4) I see reference to Table 2, 3, which I saw in the list of steps.  Are these like sub-routines?

    You can see as sub-routines if this helps you understand. Actually, for each "group", I create a separate table with the same number of columns (1st contains the record type valued "concatenated", 2nd is a first Index, 3rd is a second Index) to be able to Append them (the Tables) almost at the end (to APPEND them tables must have the same number of columns with the same names)

    5)...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

    The Advance Editor only shows you the code while the Steps in the main window show you the Output of the corresponding code Step once executed

    Was this answer helpful?

    0 comments
  3. Anonymous
    2018-07-30T11:53:22+00:00

    OK I realise this is pushing the boundary!  Is it possible to add one additional group to see how that goes?  Or to ask another question, how many additional groups do you think would be reasonable to add? 

    We can load changes (the 00EMPLOYEE section would have the employee number instead of being blank) so I could feasibly split the work into a couple of uploads to prevent the need to add on 20-odd groups.

    Was this answer helpful?

    0 comments
  4. Lz365 38,201 Reputation points Volunteer Moderator
    2018-07-30T12:16:26+00:00

    Is it possible to add one additional group to see how that goes?  Or to ask another question, how many additional groups do you think would be reasonable to add? 

    The number of total "groups" doesn't really matter. You could have 3 or 23 this doesn't change the fact that currently the query is kind of hard-coded with 3 "groups". So, as it is, if you only have 2 groups of columns (say 00EMPLOYEE, 10PERDET but not 35EMPBASIC) the query will fail. Hope you see what I mean

    Let me think about this a moment...

    Was this answer helpful?

    0 comments