Forum Discussion
Resolver IHow to import excel creation date from sharepoint into data table in Power BI
Hi,
I have an excel sheet in sharepoint that I want to import into power bi, I would also like to create a column once it's imported to store the date the file was created.
I have several steps in power query to filter for the correct excel.
The creation date of the file can be seen in the "Filtered Rows 2" below, is there a way I can create a new step after the "Added Custom" Step to add the this date to the data table?
Thanks
Hi obriaincian
You can add a custom column using the Custom Column dialog box, where the expression to use for the column references the "Filtered Rows2" step, Date Created column first row.
The formula to enter in the dialog box would be:
= #"Filtered Rows2"[Date created]{0}The code in the formula bar would be something like this:
= Table.AddColumn(#"Added Custom", "Date created", each #"Filtered Rows2"[Date created]{0}, type datetime)Here's a screenshot from a test query I created:
Does this work?
Regards,
3 Replies
- OwenAuger
Super User
Hi obriaincian
You can add a custom column using the Custom Column dialog box, where the expression to use for the column references the "Filtered Rows2" step, Date Created column first row.
The formula to enter in the dialog box would be:
= #"Filtered Rows2"[Date created]{0}The code in the formula bar would be something like this:
= Table.AddColumn(#"Added Custom", "Date created", each #"Filtered Rows2"[Date created]{0}, type datetime)Here's a screenshot from a test query I created:
Does this work?
Regards,
obriaincianOwenAuger thank you for the above, I am having an issue however, when I try to create a custom column the page just stays on the loading phase and the column never gets created.
- OwenAuger
No problem, and sorry to hear that there seems to be a performance issue when adding this step.
One thing you could try:
1. Select the "Filtered Rows2" step
2. Edit the M code in the formula bar by wrapping it in Table.Buffer( ... 😞
= Table.Buffer ( Table.SelectRows(#"Filtered Rows1", let latest = List.Max(#"Filtered Rows1"[Date created]) in each [Date created] = latest) )3. Then add the "Date created" column as described earlier.
The idea her is to buffer the step that contains "Date created". This should prevent the query re-querying SharePoint just to fetch that date.
Does this work any better?
Another option would be to modify the steps of your query so that the "Date created" column remains in the table from step "Filtered Rows2" onwards, but you would need to tweak the steps transforming the Excel file. If you want to try that, could you post your entire M code here (if possible)?
Regards