XML data to Table using Power Query

This is my current query, which returns the dataset below.

let
BaseLink = “https://web-api.tp.entsoe.eu/api?securityToken=SECURITY_TOKEN”,
QueryParamDocType = “&documentType=A44”,
QueryParamProcessType = “&processType=A62”,
QueryParamRegion = “&in_Domain=10YNL———-L&out_Domain=10YNL———-L”, // Netherlands
QueryParamStartTime = “&periodStart=202405312300”, // 31st of May 2024, 23:00
QueryParamEndTime = “&periodEnd=202406302300”, // 30th of June 2024, 23:00

(Not worrying about the column names right now, I will fix those once I’ve got everything in a table)

There’s a start record every 49 rows and in between those are 24 rows with position, which indicate the hour of the day and 24 rows with price.amount, which has the value I need for calculating the hourly prices.

I’d like to have a table that looks somewhat akin to this:

StartPositionPrice.Amount2024-05-31T22:00167.862024-05-31T22:00267.77……

This Query is somewhat dynamic. I want to be able to retreive data from different dates, without having to do a lot of work in dissecting a table.

I have a query to get XML web contents which I would like to use in a table in my worksheets. I’ve managed to filter out all the unneeded and unnecessary data from the resultset, but I don’t know how to convert the current data I have into an actual table.This is my current query, which returns the dataset below. let
BaseLink = “https://web-api.tp.entsoe.eu/api?securityToken=SECURITY_TOKEN”,
QueryParamDocType = “&documentType=A44”,
QueryParamProcessType = “&processType=A62”,
QueryParamRegion = “&in_Domain=10YNL———-L&out_Domain=10YNL———-L”, // Netherlands
QueryParamStartTime = “&periodStart=202405312300”, // 31st of May 2024, 23:00
QueryParamEndTime = “&periodEnd=202406302300”, // 30th of June 2024, 23:00

Source = Xml.Document(Web.Contents(
BaseLink &
QueryParamDocType &
QueryParamProcessType &
QueryParamRegion &
QueryParamStartTime &
QueryParamEndTime)),
#”Removed Columns” = Table.RemoveColumns(Source,{“Name”, “Namespace”, “Attributes”}),
#”Expanded Value” = Table.ExpandTableColumn(#”Removed Columns”, “Value”, {“Value”}, {“Value.Value”}),
#”Removed Top Rows” = Table.Skip(#”Expanded Value”,8),
#”Expanded Value.Value” = Table.ExpandTableColumn(#”Removed Top Rows”, “Value.Value”, {“Value”}, {“Value.Value.Value”}),
#”Removed Top Rows1″ = Table.Skip(#”Expanded Value.Value”,2),
#”Removed Alternate Rows” = Table.AlternateRows(#”Removed Top Rows1″,0,7,1),
#”Expanded Value.Value.Value” = Table.ExpandTableColumn(#”Removed Alternate Rows”, “Value.Value.Value”, {“Value”}, {“Value.Value.Value.Value”}),
#”Removed Alternate Rows1″ = Table.AlternateRows(#”Expanded Value.Value.Value”,1,1,25),
#”Expanded Value.Value.Value.Value” = Table.ExpandTableColumn(#”Removed Alternate Rows1″, “Value.Value.Value.Value”, {“Name”, “Value”}, {“Value.Value.Value.Value.Name”, “Value.Value.Value.Value.Value”}),
#”Removed Alternate Rows2″ = Table.AlternateRows(#”Expanded Value.Value.Value.Value”,1,1,49)
in
#”Removed Alternate Rows2″ (Not worrying about the column names right now, I will fix those once I’ve got everything in a table) There’s a start record every 49 rows and in between those are 24 rows with position, which indicate the hour of the day and 24 rows with price.amount, which has the value I need for calculating the hourly prices.I’d like to have a table that looks somewhat akin to this:StartPositionPrice.Amount2024-05-31T22:00167.862024-05-31T22:00267.77……This Query is somewhat dynamic. I want to be able to retreive data from different dates, without having to do a lot of work in dissecting a table. Read More

Cart

Cart