Unpivot Calendar to Table

[AllisterB]

Hi

I have a calendar of guests arriving on a day and those leaving on that day.

I want to have it one taable with columns

Date
In
Out
Name

the file attached shows how far I have got so far.

i do not know how to bring the four columns of Dates into one Column - I suspect it is using one of the options for Unpivot.

Any solutions - NB I need to use this with PQ in Excel

Thanks

ACBK.xls (65.5 KB)

[Melissa]

Hi @AllisterB,

This proved to be a challenging one, mainly because the data wasn’t only stacked vertically across rows but also repeated horizontally across columns.

1. Since the number of rows wasn’t distributed evenly, I removed “all blank rows”.
2. Also removed the top 2 rows AND the first column
   
   
3. Transposed the Table
4. Filled down all date columns in the table, so we have values for each row
   
   
5. Table.ToColumns; returning a List for each column of the table (in the same order)
   
   
6. To be able to identify the Lists that belong together - (A) added an Index, (B) extracted the first value from the List object and if that was a date (C ) return the value from [Index] and Fill Down.
   
7. Now to bring them back together a very special Group By (kudos to Imke Feldmann):
   Table.Group(#“Filled Down”, {“Index2”}, {{“Partition”, each Table.FromColumns(_[Column1]), type table [Column1=list, Index2=number]}})
   
   
8. and combining the nested tables into a single table:
   Table.Combine(#“Grouped Rows”[Partition])
   
   
9. Now all that’s left to do, Unpivot Other Columns
10. Removed the Attribute column
11. Renamed AND Changed the Type
    

Here is the Excel file containing the Query. eDNA - calendar to table.xls (31.1 KB)
I hope this was helpful.

[haroonali1000]

Wow @Melissa Great solution!

[BrianJ]

@Melissa,

Agreed - this is amazing. From @AllisterB‘s initial description, I thought “this doesn’t sound too hard”. Then, I opened the file and was like “whoa, nope”. Thanks for posting such detailed solutions and clear explanations - these are like daily mini tutorials on PQ/M and I’ve learned a ton from them. Great stuff!

• Brian

[EnterpriseDNA]

Hi @AllisterB, a response on this post has been tagged as “Solution”. If you have a follow question or concern related to this topic, please remove the Solution tag first by clicking the three dots beside Reply and then untick the check box. Thanks!"

[AllisterB]

Thank You so much for your fantastic solution - nice work.

I have implemented it for one instance. I now need to use it for 6 workbooks in one folder. They all have the same structure.
I tried replacing the source code in your solution with code that combines data from all the workbooks . the code is attached and it doesn’t work as iI get this error
“Expression.Error: The name ‘Sample File’ wasn’t recognized. Make sure it’s spelled correctly.”

Can you suggest a way I can do it successfully?

Thanks again.

Allister

acb.txt (3.4 KB)

[marcster_uk]

To merge files in a single folder to on dataset…

Import data from a folder with multiple files (Power Query) - Microsoft Support

[Melissa]

Hi @AllisterB,

Updated the Excel file for the FromFolder option. It references Tables named Table1, so check that.
Just update the FileFolderLocation Parameter and the Queries should restore.

eDNA - calendar to table FromFolder.xls.xlsx (119.8 KB)

[AllisterB]

Thanks Melissa

i have Changed the Parameter to the fi older containing teh workbook and each workbook holds thedata in a Table “Table1” on Sheet1.

However I get the error on the attached file.

Can you assist ?

Thank You

Allister

In FromFolder Query.docx (31.0 KB)

[Melissa]

Well the error occurs when the Custom Function is called, so you actually need to step through that Query to see where the error occurs… Can you do that and let me know?

[AllisterB]

Hi Melissa

In theTransform File Query there is only 1 applied step. When I go to teh editor I see 20 or so rows of code - how to I step through these to find where the error is

Allister

[Melissa]

On the right of your screen there should be an “Applied Steps” pane…

If that isn’t visible on your screen go to the File menu: Options and Settings / Query Options / Power Query Editor => and toggle on the “Display the Query settings pane”

Start on the top and work your way down, so click on each Name and when you encounter an Error make sure to include a screen print of the preview pane as well

[AllisterB]

Hi

It occurs on the second step of Transform Sample File

[Melissa]

What do you see in the preview pane? That’s the center of your screen.
In this case also a view of the preview pane of the previous step will be helpful - thanks

[AllisterB]

see attached

BTW I have renamed the sheet containing the data in each of eth workbooks with the name Data.

In FromFolder Query 2.docx (108.8 KB)

[Melissa]

please include the previous step as well, thanks

[AllisterB]

Hi Melissa

Here is the preview of thefirst step “Source”

[Melissa]

Okay so what the Navigation step is doing is:

• looking for the [Item] called “Table1”
• and filtering on [Kind] equal to “Table”
• From that Record extract the [Data] column “Table”
  
  

Now these first two columns aren’t visible in the screenshot you provided, please check that - thanks

[AllisterB]

I have attached eth code - the line in you last post is the second row

I expanded the Data column and that just gave me the columns from the data Worksheet. The screen showing Dat Item Kind eg is not showing - do I need to do anything to make it show

Allister

In FromFolder Query 3.docx (13.0 KB)

[Melissa]

I need to see the preview pane for the Source step, as I tried to explain the combination of (1) and (2) result in getting the Data at position (3). So if Kind <> Table or the Item <> Table1 - you’ll get that error

If the only columns you have in the Source step are depicted in post #16 than see what happens if you manually add a New Source/File/Folder, select the Parameter when asked for the Folder path and choose Combine and Transform data, you will get a screen like this.

Select Table1 and click OK

A new Helper Queries folder is created

Now check the Source step for the Transform Sample Query - that should look like mine.