Unpivot Calendar to Table

Power Query
41

image
image1374Ɨ172 8.49 KB

42

You have a Parameter1 so change it from Parameter2 into Parameter1
Iā€™ve updated it as well in post #38

43

Hereā€™s what Ive got -t the output of Transform Sample File - Yah

Now I have an error in FromFolder

image
image626Ɨ513 26.9 KB

44

So have you checked those steps to see where that error occurs?

45

Error message
The Data Model table couldnā€™t be refreshed:
Excel couldnā€™t refresh the table ā€˜FromFolderā€™ from connection ā€˜Query - FromFolderā€™. Hereā€™s the error message from the external data source:

46

The Transform Sample File Query and eth FromFolder step through ok - its seems to be the Load to data Model error ā€¦?

47

Think that might have happend when we changed to xlsx

Hereā€™s my code for that Query, does that differ from yours?

let
    Source = Folder.Files(FileFolderLocation),
    #"Removed Other Columns" = Table.SelectColumns(Source,{"Content"}),
    #"Filtered Hidden Files1" = Table.SelectRows(#"Removed Other Columns", each [Attributes]?[Hidden]? <> true),
    #"Invoke Custom Function1" = Table.AddColumn(#"Filtered Hidden Files1", "Transform File", each #"Transform File"([Content])),
    #"Removed Other Columns1" = Table.SelectColumns(#"Invoke Custom Function1", {"Transform File"}),
    #"Expanded Table Column1" = Table.ExpandTableColumn(#"Removed Other Columns1", "Transform File", Table.ColumnNames(#"Transform File"(#"Sample File"))),
    #"Changed Type" = Table.TransformColumnTypes(#"Expanded Table Column1",{{"Date", type date}, {"In/Out", type text}, {"Name", type text}})
in
    #"Changed Type"
48

I have an error in teh Transform Sample File Query - Navigation Step

d should teh Data D Sheet name be mentioned in this ?

image

49

No this was the issue we had before you moved to xlsx

50

Just to summarise

the Two Queries we have been working on step through ok but both show Load to data Model failed

When I do a data Refresh All
I get multiple instances of

image
and

image
image859Ɨ145 3.31 KB

51

This is where you should start to look for the solution

52

Hi Melissa
I have taken far more of your time than I should. as so I think I should abandon this and do it some other way. having said taht it is not good that I have taken so much of your time for no result .

is there a way forward - - could I manually combine the w workbooks into one ???

Thank You again so much for you time Your expertise is astounding.

Good Night

Kind Regards

Allister

53

I do believe weā€™ve created a Forum record. :birthday: :tada:
And remain convinced this can be done. So have a good nights rest and maybe revist this with fresh eyes - All the best Allister.

54

Good Morning ? Melissa

Thanks for your positive message - although I do not want to be known as the guy who set a forum record.

I can probably afford to spend another hour on this.

What do you think about sending a copy of your workbook - and I take all I will need to do now is just alter the parameter. I this way you will know exactly what code i am starting with.

What do you think of this approach.

Kind regards

Allister

55

:wink:

Sure thing, here it is: eDNA - calendar to table FromFolder.xlsx (119.9 KB)

Important notes.

  • The source files in the Folder need to have the extension .xlsx
  • and need to have a Table called Table1, which contains the data
56

Hi Mewlissa

Can the workbooks have a different number of rows ?

Allister

57

That should be fine, now the Date FillDown step has been moved, but Iā€™ll do another checkā€¦

@AllisterB No issues, runs perfectly

58

Hi Melissa

Iā€™ll carefully
Update your workbook as outlined in Post 55
Check that all workbooks have a table

Does each workbook need to have the data on a Sheet named data ?
After this Iā€™ll let you know - may be a n an hour doing this

Allister

59

No the sheet name is irrelevant.

Below the files I tested with, just copies of the sample provided. You can examine them.
Place them in a folder, update the FileFolderLocation and Refresh the file from post #54.
Next youā€™ll have to choose LoadTo and materialize the Output in a Table on a New Sheet.
File1.xlsx (14.7 KB)
File2.xlsx (13.8 KB)

Once this is completed successfully just swap the files in the Folder and Refresh the Query again.
If that is giving you issues, youā€™ll now you have to examine the source files in the folder on what is outlined in post #55.

I hope this is helpful and youā€™ll be able to resolve any issues more quickly.

60

FYI

image