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

Anonymous

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

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 Volunteer Moderator

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 (_))),

...

0 comments

59 additional answers

Anonymous

2018-07-17T13:54:48+00:00

Hello,

I have a more realistic example. Basically this is staff data so each employee has a record on the database. With other databases I've used there would be separate APIs to upload (eg) personal data, address data, job data, etc, but this database allows everything to be uploaded together so long as each record type is on a new line in the csv file. Hence my issue.

This file is my Excel spreadsheet where I'm working on all the data in one place.

https://www.dropbox.com/s/oy8izs24u1jaiiy/LR%20csv%20query%20in%20Excel.xlsx?dl=0

I've left the header rows in to show you what data I'm dealing with but in reality they would be removed, however, the columns without data would stay otherwise the system would think surname goes in 10PERDET column 2. Therefore the record type columns are important as the tell the API that data following 00EMPLOYEE is the new record, then 10PERDET data goes into the People table, then 15ADDDET goes into the address table, etc.

If you open this file in Notepad you'll see I've put in the line breaks at the change in record type indicator but I've done this manually and I'll have 120 staff records with I'm not sure yet how many record types so I don't want to be pressing return 500-odd times!

https://www.dropbox.com/s/wrxylvqijh89g05/LR%20csv%20query%20in%20csv.csv?dl=0

I hope that makes more sense and thanks for looking at this. ;0)
Was this answer helpful?

0 comments No comments
Lz365 38,201 Reputation points Volunteer Moderator

2018-07-17T12:59:55+00:00
Hi again

Apart from the values in columns 1, 6 and 10 in your example changing

Not sure I understand. Are you saying that columns 1, 6, 10.... will always contain the same "record indicator"?

This is done (pretty manual as we speak) with Excel 2016 Get & Transform. Would it be possible you upload a realistic sample - say around 10 rows - with the actual column names… on OneDrive (or any other service) and post the link to the file in your next reply?

Other key questions:
1. Is there anything reliable that would help identifying which columns contain the "record Indicator" (1, 6, 10 in the current example)?
2. Is there a max number of columns or will this always be unknown?
With a realistic sample I might be able to build something - not guaranted - and provide you a sample...
Was this answer helpful?

0 comments No comments
Anonymous

2018-07-17T12:33:19+00:00

Hello,

Apart from the values in columns 1, 6 and 10 in your example changing, that looks like my example. The trouble is there are varying numbers of columns per record type as it depends on which table the data is being put into and the record type indicator (col 1, 6, 10) is needed to tell the API which table to put the data into. So the start of each persons record (this is staff data) will always begin with the column 1 indicator which will contain their employee number.

How did you perform this magic???
Was this answer helpful?

0 comments No comments
Lz365 38,201 Reputation points Volunteer Moderator

2018-07-17T12:07:49+00:00

Hi Louise

(not an Easy one due to the varying number of columns between the "record numbers": 01, 02...)

Assuming the following Input table

A possible Output to export as CSV could look like this (incomplete):

Would this work?
Was this answer helpful?

0 comments No comments