Forum Discussion
XML data to Table using Power Query
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:
Start | Position | Price.Amount |
2024-05-31T22:00 | 1 | 67.86 |
2024-05-31T22:00 | 2 | 67.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.
A bit difficult to describe in words, so I created a smaller scale model that I believe does what you ask for. Not very sophisticated though. See attached.
- Riny_van_EekelenPlatinum Contributor
A bit difficult to describe in words, so I created a smaller scale model that I believe does what you ask for. Not very sophisticated though. See attached.