Unpivot Calendar to Table

I have attached a screen shot of the Preview pane for the Transform sample File Query.
I have also done a Preview Refresh but get the same thing. I don’t know what I am doing wrong - I would like to find the preview that In FromFolder Query 4.docx (64.3 KB) you are looking for.

Kind regards

Allister

I believe we posted at the same time, can you check my instructions above?

At the point you asked me to select Table 1 I got this

image

So you don’t seem to have an Excel Table with the Name = Table1 in your first file
Can you check that?

See attched - this is from the Sheet named data in only file (at this stage) in the Folder called Actual Rosters
image

That’s weird… what file extension does this file have? Is it a xlsx?

Otherwise just choose Data - which is showing as a Table but you don’t seem to have any Sheets…
And show the Source step for the Transform Sample Query after - thanks again

Hi Melissa

image
I choose data and got the following in teh end …

In FromFolder Query 5.docx (117.3 KB)

Hi Melissa

I saved eth data File in teh folder as xlsx and by following your instructions got theIn FromFolder Query 6.docx (60.0 KB) following

Ok excellent, thanks for that Allister, we seem to be making some progress now.

I’ve changed the first 2 lines of M code, can you paste this in the Transform Sample Query and let me know if you run into another error?

let
    Source = Excel.Workbook(Parameter2, null, true),
    Table1_Table = Source{[Name="Data"]}[Data],
    #"Removed Top Rows" = Table.Skip(Table1_Table,2),
    #"Removed Other Columns" = Table.SelectColumns(#"Removed Top Rows",{"Column2", "Column3", "Column4", "Column5", "Column6", "Column7", "Column8", "Column9", "Column10", "Column11", "Column12", "Column13", "Column14", "Column15"}),
    #"Removed Blank Rows" = Table.SelectRows(#"Removed Other Columns", each not List.IsEmpty(List.RemoveMatchingItems(Record.FieldValues(_), {"", null}))),
    #"Transposed Table" = Table.Transpose(#"Removed Blank Rows"),
    #"Filled Down" = Table.FillDown(#"Transposed Table",{"Column1", "Column8", "Column14", "Column21"}),
    #"Table To Columns" = Table.ToColumns(#"Filled Down"),
    #"Converted to Table" = Table.FromList(#"Table To Columns", Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Added Index" = Table.AddIndexColumn(#"Converted to Table", "Index", 0, 1),
    #"Added Custom" = Table.AddColumn(#"Added Index", "Custom", each [Column1]{0}),
    #"Added Index2" = Table.AddColumn(#"Added Custom", "Index2", each if [Custom] is datetime or [Custom] is date then [Index] else null),
    #"Filled Down2" = Table.FillDown(#"Added Index2",{"Index2"}),
    #"Grouped Rows" = Table.Group(#"Filled Down2", {"Index2"}, {{"Partition", each Table.FromColumns(_[Column1]), type table [Column1=list, Index=number]}}, GroupKind.Local),
    #"Combined Grouped Rows" = Table.Combine(#"Grouped Rows"[Partition]),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Combined Grouped Rows", {"Column1", "Column2"}, "Attribute", "Value"),
    #"Removed Other Columns1" = Table.SelectColumns(#"Unpivoted Other Columns",{"Column1", "Column2", "Value"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Other Columns1",{{"Column1", "Date"}, {"Column2", "In/Out"}, {"Value", "Name"}}),
    #"Change Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"Date", type date}, {"In/Out", type text}, {"Name", type text}})
in
    #"Change Type"

do I need to do that if I have changed teh file format of eth data file to xlsx

That depends, when you repeat the steps do you get a Source step that looks like this:

If it still looks like below - than you do…
image

But you can paste the entire M code in the Advanced Editor

In posting 27 I sent a screen shot - is this what you were looking for ?

Yes!

Noticed that yours referenced Parameter2 and mine Parameter1 - so that’s what I’ve changed below.
Paste this M code in your Transform Sample Query and Step through it (using the Applied Steps) let me know if you see any errors?

let
    Source = Excel.Workbook(Parameter2, null, true),
    Table1_Table = Source{[Item="Table1",Kind="Table"]}[Data],
    #"Removed Top Rows" = Table.Skip(Table1_Table,2),
    #"Removed Other Columns" = Table.SelectColumns(#"Removed Top Rows",{"Column2", "Column3", "Column4", "Column5", "Column6", "Column7", "Column8", "Column9", "Column10", "Column11", "Column12", "Column13", "Column14", "Column15"}),
    #"Removed Blank Rows" = Table.SelectRows(#"Removed Other Columns", each not List.IsEmpty(List.RemoveMatchingItems(Record.FieldValues(_), {"", null}))),
    #"Transposed Table" = Table.Transpose(#"Removed Blank Rows"),
    #"Filled Down" = Table.FillDown(#"Transposed Table",{"Column1", "Column8", "Column14", "Column21"}),
    #"Table To Columns" = Table.ToColumns(#"Filled Down"),
    #"Converted to Table" = Table.FromList(#"Table To Columns", Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Added Index" = Table.AddIndexColumn(#"Converted to Table", "Index", 0, 1),
    #"Added Custom" = Table.AddColumn(#"Added Index", "Custom", each [Column1]{0}),
    #"Added Index2" = Table.AddColumn(#"Added Custom", "Index2", each if [Custom] is datetime or [Custom] is date then [Index] else null),
    #"Filled Down2" = Table.FillDown(#"Added Index2",{"Index2"}),
    #"Grouped Rows" = Table.Group(#"Filled Down2", {"Index2"}, {{"Partition", each Table.FromColumns(_[Column1]), type table [Column1=list, Index=number]}}, GroupKind.Local),
    #"Combined Grouped Rows" = Table.Combine(#"Grouped Rows"[Partition]),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Combined Grouped Rows", {"Column1", "Column2"}, "Attribute", "Value"),
    #"Removed Other Columns1" = Table.SelectColumns(#"Unpivoted Other Columns",{"Column1", "Column2", "Value"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Other Columns1",{{"Column1", "Date"}, {"Column2", "In/Out"}, {"Value", "Name"}}),
    #"Change Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"Date", type date}, {"In/Out", type text}, {"Name", type text}})
in
    #"Change Type"

I get a result from the one table I have in the folder - Yah !

When I add a second the new file causes an error - the code expects a Column 14 [#“Filled Down” = Table.FillDown(#“Transposed Table”,{“Column1”, “Column8”, “Column14”, “Column21”}),] and there isn;t one. i also get th same error if i only have eth second file in the folder.

That’s because the lay-out of the data in the table(s) isn’t consistent.
Would it be possible to create a template file with a fixed number of rows for the weeks (doesn’t matter how many)? So that when we remove the “Remove blank rows” step and flip the table on it’s side - the Dates will always end up in the same column.

OR

If that’s not really an option I can check if I can move the fill down step to the nested Lists - I’ll check that now and get back to you

That would be great - a better solution - do you think its poss ?

Hi Melissa

BTW what time zone are you in ?
I am in New Zealand

…And here’s the verdict (drum roll please) yes it’s possible!

So from the point where we had split up our Table into Lists and Added the Index
image

I had to turn these Lists back to a Table, because there is no Fill down for type List

So here I’m checking if the first value from the first column of this Table is of type date
and when it is Fill Down the values in that Column - if it’s not just return the Table
finally by adding the column reference - extracting the first colum - turning it back into a List

followed by the other steps originally from post #2
All put together you’ll get this M code, so past that in and let me know if there are any problems

let
    Source = Excel.Workbook(Parameter1, null, true),
    Table1_Table = Source{[Item="Table1",Kind="Table"]}[Data],
    #"Removed Top Rows" = Table.Skip(Table1_Table,2),
    #"Removed Other Columns" = Table.SelectColumns(#"Removed Top Rows",{"Column2", "Column3", "Column4", "Column5", "Column6", "Column7", "Column8", "Column9", "Column10", "Column11", "Column12", "Column13", "Column14", "Column15"}),
    #"Removed Blank Rows" = Table.SelectRows(#"Removed Other Columns", each not List.IsEmpty(List.RemoveMatchingItems(Record.FieldValues(_), {"", null}))),
    #"Transposed Table" = Table.Transpose(#"Removed Blank Rows"),
    #"Table To Columns" = Table.ToColumns(#"Transposed Table"),
    #"Converted to Table" = Table.FromList(#"Table To Columns", Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Added Index" = Table.AddIndexColumn(#"Converted to Table", "Index", 0, 1),
    #"ToTable step" = Table.Group(#"Added Index", {"Index"}, {{"ToTable", each Table.FromColumns(_[Column1]), type table [Column1=list, Index=number]}}, GroupKind.Local),
    #"FillDownDates step" = Table.AddColumn(#"ToTable step", "FillDownDates", each if [ToTable][Column1]{0} is datetime or [ToTable][Column1]{0} is date then Table.FillDown([ToTable],{"Column1"})[Column1] else [ToTable][Column1]),
    #"Added Index2" = Table.AddColumn(#"FillDownDates step", "Index2", each if [FillDownDates]{0} is datetime or [FillDownDates]{0} is date then [Index] else null),
    #"Filled Down2" = Table.FillDown(#"Added Index2",{"Index2"}),
    #"Grouped Rows" = Table.Group(#"Filled Down2", {"Index2"}, {{"Partition", each Table.FromColumns(_[FillDownDates]), type table [FillDownDates=list, Index2=number]}}, GroupKind.Local),
    #"Combined Grouped Rows" = Table.Combine(#"Grouped Rows"[Partition]),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Combined Grouped Rows", {"Column1", "Column2"}, "Attribute", "Value"),
    #"Removed Other Columns1" = Table.SelectColumns(#"Unpivoted Other Columns",{"Column1", "Column2", "Value"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Other Columns1",{{"Column1", "Date"}, {"Column2", "In/Out"}, {"Value", "Name"}}),
    #"Change Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"Date", type date}, {"In/Out", type text}, {"Name", type text}})
in
    #"Change Type"

On the Source Line

Expression.Error: The name ‘Parameter2’ wasn’t recognized. Make sure it’s spelled correctly.

Ok so look in your Helper Queries folder, does that contain a Parameter1? Than change the name.
image