Retrieveing INSEE data in Excel via Power query

[Cyssou]

Hi I'm looking a way to connect INSEE data in Excel using Power Query but I don't understand how to connect Power Query

If I use VB code I correctly get data in imported in an excel sheet but I would prefer to get a connected source : any idea ?

VB code :

Sub Series()
Dim IdBank As Variant, i As Byte

IdBank = Array("010534284")

For i = 0 To UBound(IdBank)
Import IdBank(i)
Next i
End Sub

Sub Import(Idbk As Variant)
Dim Site As String, Sht As Worksheet

Site = "https://bdm.insee.fr/series/sdmx/data/SERIES_BDM/" & Idbk

Application.DisplayAlerts = False
Set Sht = Sheets.Add(, ActiveSheet)
Sht.Name = Idbk
With Sheets(Idbk)
ThisWorkbook.XmlImport Url:=Site, ImportMap:=Nothing, Overwrite:=True, _
Destination:=.Range("A3")

.Range("A1").Value = "Mise à jour du :"
.Range("A1").HorizontalAlignment = xlRight
.Range("B1").Value = Format(Now(), "dd/mm/yyyy")
.Range("C1").Value = Format(Now(), "hh:nn")
End With
Application.DisplayAlerts = True
Set Sht = Nothing
End Sub

[#2 · Accepted solution]

@Cyssou Perhaps the attached file helps you to get started with PowerQuery.

Connecting to the site with the specified Idbank-code produces a list of indices for the past 17.5 years, but perhaps I didn't find all the data "hidden" in the XML source. Enter an Idbank-code on the first sheet. On the data ribbon, press Refresh All and the data will be updated in the table on the Data sheet.

[#3]

@Riny_van_Eekelen thanks this is really helpfull !!