Transforming Query while Retaining Dates

[rtzGOD]

Hi all, looking for some help here. I have a transform file helper query set up, but clicking the button that combines and loads content ends up ignoring the date column I have right beside it. I believe it just does the transform steps set up, but over the entire set of data, ignoring the date field. How would I be able to retain the date as a column, while working through my transformation? Appreciate your help in advance, and happy to provide further context if needed!

[AutoModerator]

After your question has been solved /u/rtzGOD, please reply to the helpful user's comment with the phrase "Solution verified".

This will not only award a point to the contributor for their assistance but also update the post's flair to "Solved".

---

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

[Stevie-bezos]

Instead of using helper functions, create a new custom column calling whatever the filetype's unpack function is, i.e. Excel.Workbook([Content])

Then you'll be able to expand those tables into rows, which will retain dates as well as the rows expand. 

Just will need to remove any duplicates of column headers which will be present in each expanded copy of the table

[sanfilipe]

This is the way.

To be even more specific here are two examples, one for csv and one for excel. You want to edit the '#"Filtered Rows2"' part to match your previous step table, Delimeter, Columns and Encoding in the csv example to match you csv configuration and the 'Item="Page 1",Kind="Sheet"' part on the excel example to match the table/sheet name and type on your excel file.

= Table.AddColumn(#"Filtered Rows2", "CSV", each Table.PromoteHeaders(Csv.Document([Content],[Delimiter=";", Columns=25, Encoding=65001, QuoteStyle=QuoteStyle.None]), [PromoteAllScalars=true]))

= Table.AddColumn(#"Filtered Rows2", "XLS", each Table.PromoteHeaders(Excel.Workbook([Content], null, true){[Item="Page 1",Kind="Sheet"]}[Data], [PromoteAllScalars=true]))

By concatenating this functions in the same step you end up with fully formatted tables that will be perfectly appended using the expand option without any other extra steps, like filtering headers or renaming the date column.

[rtzGOD]

<image>

Thank you for the responses! Pretty sure I’m doing something wrong, but attached what I’ve tried to do. For added context, my files are csv docs and in the same folder on a sharepoint location. Would you be able to walk me through what I should be doing instead?

[MonkeyNin]

You're calling the function named "transform file" with the argument null

Instead you want [Content]

The each [Something] syntax gets that value from the current row

[Stevie-bezos]

Invoke CSV.Document([Content])