Forum Discussion

obriaincian's avatar
obriaincian
Resolver I
3 years ago
Solved

How 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 ste...
Need Help
  • OwenAuger's avatar
    OwenAuger
    3 years ago

    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,

obriaincian's avatar
obriaincian
Resolver I
3 years ago

OwenAuger 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's avatar
OwenAuger
Super User
3 years ago

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