Pivoting multiple files into one

0
votes

I am facing a challenge right now.

I need to combine various CSV into one database. The "catch" is:

  1. The files have the columns as rows;

  2. The files may have different number of rows (columns).

Here is an example below:

File1

Agent Name,xpto1
Agent Email,[email protected]
Date,04/18/2019 14:58:25
Time Zone,Europe/Lisbon
Time Filter Begin,04/17/2019 00:00:00
Time Filter End,04/17/2019 23:00:00
Total Unavailable Time (hh:mm:ss),00:00:00
Total Offline Time (hh:mm:ss),00:47:50

File2

Agent Name,xpto2
Agent Email,[email protected]
Date,04/18/2019 14:58:25
Time Zone,Europe/Lisbon
Time Filter Begin,04/17/2019 00:00:00
Time Filter End,04/17/2019 23:00:00
Total Auto Answered Calls Notifications Offered,27
Total Number of Calls Notification Offered,42
Total Auto Answered Calls Connected,15
Total Number of Calls Connected,15
Total Number of Multicast Call Notifications Offered,15
Total Unavailable Time (hh:mm:ss),00:02:32
Total Offline Time (hh:mm:ss),14:54:48
Total Wrap-up Time (hh:mm:ss),01:40:37
Total Email Time (hh:mm:ss),00:42:50
Total In-call Time (hh:mm:ss),03:57:42
Total Available Time (hh:mm:ss),01:04:56
Total Meal Time (hh:mm:ss),01:00:59
Total Break Time (hh:mm:ss),00:35:39

Basically I need to combine a hundred (give or take) of these files in a database format

Column 1, Column2, etc, with the correspondent values below each column, with nulls/zeros in case some file does not have a row that other may have.

Any clue how to do this?

I've tried to pivot all the files after merging but I am getting an error:

"Expression.Error: There were too many elements in the enumeration to complete the operation. Details: List"

Thanks in advance!

1
powerbipowerquerym

1 Answers

0
votes

If you have all of these files in a folder, then you can connect to that folder.

Folder Contents

If you click that double down arrow in the Content column, then it should automatically do a bunch of steps to get you to a table looking like this:

Expand Column

From here, you want to pivot Column1 using Column2 as the Values Column and Don't Aggregate under Advanced options. This should give you what you're looking for:

Result