Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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,
Solved! Go to 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.
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
102 | |
53 | |
21 | |
13 | |
11 |