Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Sharepoint online lists - new data everyday

I have a dataset where I am reporting on Operational Events over a 24 hour period. One of the data sources is customer contact data which is deleted automatically at 2am and then refreshed with new d...
Need Help
Tips and Tricks
  • lbendlin's avatar
    lbendlin
    6 years ago

    Check your auto generated M Code.  It probably looks like this:

     

    let
    Source = SharePoint.Tables("https://xxx.sharepoint.com/teams/yyy", [ApiVersion = 15]),
    #"463db794-eb4d-45b4-8ad4-818c002c3811" = Source{[Id="463db794-eb4d-45b4-8ad4-818c002c3811"]}[Items],
    #"Renamed Columns" = Table.RenameColumns(#"463db794-eb4d-45b4-8ad4-818c002c3811",{{"ID", "ID.1"}})
in
    #"Renamed Columns"

     

    Change it to key off the name instead of the Id (hopefully the list name is constant?!)

     

    let
    Source = SharePoint.Tables("https://xxx.sharepoint.com/teams/yyy", [ApiVersion = 15]),
    MyList = Source{[Title="My List Name"]}[Items],
    #"Renamed Columns" = Table.RenameColumns(MyList,{{"ID", "ID.1"}})
in
    #"Renamed Columns"

     

Greg_Deckler's avatar
Greg_Deckler
Icon for Community Champion rankCommunity Champion
6 years ago

Anonymous - Are you doing any transformation in your query or is just a straight-up load of the SharePoint list? Can you post your query code from Advanced Editor?

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Greg_Deckler 

Its a straighup load of the list and I just select the columns I want 

 

 

let
Source = SharePoint.Tables("https://XXXX.sharepoint.com/sites/XXXX/", [ApiVersion = 15]),
#"18b51dfb-6739-4164-b7ae-faf433095086" = Source{[Id="18b51dfb-6739-4164-b7ae-faf433095086"]}[Items],
#"Renamed Columns" = Table.RenameColumns(#"18b51dfb-6739-4164-b7ae-faf433095086",{{"ID", "ID.1"}}),
#"Removed Other Columns" = Table.SelectColumns(#"Renamed Columns",{"Title", "NeedGroup", "NeedType", "Need", "ContactType", "StreetName", "Postcode", "ContactDateTime", "Modified", "Created"})
in
#"Removed Other Columns"

 

The list is called Customer Contacts 

  • mahoneypat's avatar
    mahoneypat
    Icon for Microsoft Employee rankMicrosoft Employee
    6 years ago

    You might consider making a Flow that saves all the list contents daily into a csv file stored on SharePoint or OneDrive.  You can then use the latest file as your source for Power BI, and you would have historical data files if needed too.  If the GUID is changing (which is surprising), you could have the Flow first get the list of lists from the Site and filter to it by name, then Get Items, ...

     

    If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

    Regards,

    Pat