Odata doesnt work

[Anonymous]

Hi guys,

I'm trying to use the dataset from a OpendataSoft website.
Here is the website: https://ressources.data.sncf.com/explore/dataset/mouvements-sociaux-depuis-2002/api/?sort=date_de_de...

Here is the API call: https://ressources.data.sncf.com/explore/dataset/mouvements-sociaux-depuis-2002/api/?sort=date_de_de...

I tried to use Odata sources and it doesnt match.
When I do try with "Web content", I get this kind of request:

let
    Source = Json.Document(Web.Contents("https://ressources.data.sncf.com/api/records/1.0/search/?dataset=mouvements-sociaux-depuis-2002&sort=date_de_debut&facet=date_de_debut&facet=date_de_fin&facet=organisations_syndicales&facet=metiers_cibles"))
in
    Source

Unfortunately, I don't get how to reach all the dataset.

How would you please do it?

At the moment, I just understood the possibility to drill down, manually, for each record. It would look like this for the 1rst record. And I didnt introduce the necessity to append all the records.

let
    Source = Json.Document(Web.Contents("https://ressources.data.sncf.com/api/records/1.0/search/?dataset=mouvements-sociaux-depuis-2002&sort=date_de_debut&facet=date_de_debut&facet=date_de_fin&facet=organisations_syndicales&facet=metiers_cibles")),
    records1 = Source[records],
    records2 = records1{0}
in
    records2

Please let me know, I would like to become better 🙂

Sincerely,

[dax · Accepted solution]

Hi Bryan75, 

I am not sure what is your dataset value, you could try below M code to see whether it work or not.

let
    Source = Json.Document(Web.Contents(" https://ressources.data.sncf.com/api/records/1.0/search/?dataset=mouvements-sociaux-depuis-2002&sort=date_de_debut&facet=date_de_debut&facet=date_de_fin&facet=organisations_syndicales&facet=metiers_cibles")),
    #"Converted to Table" = Record.ToTable(Source),
    #"Pivoted Column" = Table.Pivot(#"Converted to Table", List.Distinct(#"Converted to Table"[Name]), "Name", "Value"),
    #"Expanded records" = Table.ExpandListColumn(#"Pivoted Column", "records"),
    #"Expanded facet_groups" = Table.ExpandListColumn(#"Expanded records", "facet_groups"),
    #"Expanded records1" = Table.ExpandRecordColumn(#"Expanded facet_groups", "records", {"datasetid", "recordid", "fields", "record_timestamp"}, {"records.datasetid", "records.recordid", "records.fields", "records.record_timestamp"}),
    #"Expanded records.fields" = Table.ExpandRecordColumn(#"Expanded records1", "records.fields", {"date_de_debut", "motif", "organisations_syndicales", "taux_grevistes"}, {"records.fields.date_de_debut", "records.fields.motif", "records.fields.organisations_syndicales", "records.fields.taux_grevistes"}),
    #"Expanded facet_groups1" = Table.ExpandRecordColumn(#"Expanded records.fields", "facet_groups", {"facets", "name"}, {"facet_groups.facets", "facet_groups.name"}),
    #"Expanded facet_groups.facets" = Table.ExpandListColumn(#"Expanded facet_groups1", "facet_groups.facets"),
    #"Expanded facet_groups.facets1" = Table.ExpandRecordColumn(#"Expanded facet_groups.facets", "facet_groups.facets", {"count", "path", "state", "name"}, {"facet_groups.facets.count", "facet_groups.facets.path", "facet_groups.facets.state", "facet_groups.facets.name"})
in
    #"Expanded facet_groups.facets1"

Best Regards,
Zoe Zhi

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.