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. Anonymous
    2018-07-31T11:16:08+00:00

    Hello,

    Great news, that worked, well done!  Repetitive is fine, it's time to pass the baton to me!  ;0)

    I understand what you say about the delimiter.  I've used the pipe before to be on the safe side, although we try to ensure there are no commas in this kind of data.

    Was this answer helpful?

    0 comments
  2. Lz365 38,201 Reputation points Volunteer Moderator
    2018-07-31T07:59:21+00:00

    Good Morning Louise

    (I had an Internet connection outage until this morning). While I couldn't post I reviewed the whole logic (not how to treat the "Record Types") to make things more flexible

    In this Zip you'll find the old version and the new one that currently should well manage up to 4 groups of "Record Types". Could you test and confirm please? If you face an issue please provide me a sample of 4 groups so I can test and fix.

    You will notice I renamed some steps and variable names to make it more clear re. your API (i.e. Record Type). Another quite important change is the delimiter between values. So far I used the default one (the comma) and realised this could cause problem if any of your input strings has a comma somewhere (i.e. in an address field). The comma delimiter is now replaced with the pipe (|)

    Assuming this will work as expected as soon as I have your Go I'll explain how to add more "groups". With the way it's now setup you should be able to add up to your 23 "Record Types" groups. That's however going to be repetitive :-(

    Was this answer helpful?

    0 comments
  3. Anonymous
    2018-07-30T21:35:19+00:00

    Hi,

    I looked at this at home this evening.  I added a couple of extra columns to the input table with a new record type and some of the steps automatically adjusted (source, ColNamesList, DistinctCol, NbDistinct, TableRecords) but not the rest as I can see they relate to each group.  

    I looked at the code under Advanced Editor and could see lots of patterns so had a go at changing this where I thought it should be changed in a Word doc.  I copied the 3rd Group section of code then I've highlighted in red the bits I changed to move it up to be 4th Group.  There were a couple of numbers that I couldn't see what they should be changed to so they're in yellow with purple explanation at the end of the line.

    https://www.dropbox.com/s/16nbc3vr8ex2gqd/Code%20update%20for%20new%20table.docx?dl=0

    Would this need to be done to add each new group on?  Should it be done directly in the code or does the adding in of new steps create the code?  I didn't have time to play with it that way.

    Was this answer helpful?

    0 comments
  4. Anonymous
    2018-07-30T14:27:54+00:00

    Hi,

    I don't think I said what I was thinking on my last reply. 

    If I understand you correctly you know how to code this the way you have but you're aware there is a next level coding format (the then .. else .. if form) that sounds more compact/direct/multi-tasking/some might say sophisticated (?).  So, am I right in thinking the task of adding the additional groups is rather laborious?  Will this query still work in the way currently set with additional groups after the hassle of adding them on?  Or will it only work with more groups if in the conditional code branching format?  

    If it will still work in the current format and the task of adding on the groups is simple/formulaic but laborious I am offering to do this task if you can tell me the steps to take, eg copy and paste the last group section, rename for the next sequential group, increase the DistinctCol number by one, etc.

    What do you think?

    Was this answer helpful?

    0 comments