Power Query – How to expand and transpose a column table.

[shaase4]

I use this query on our SharePoint Online (SPO) Hub which has 440 Sites:

https://<hostname>/_api/search/query?querytext='contentclass%3dsts_site AND departmentid%3d{<hubsite-id>}'&refinementfilters='departmentid:string("{*"%2clinguistics%3doff)'&trimduplicates=false&rowlimit=500

This provides a complicated XML document that renders a ton of information in the Browser.  I copy all that and paste into NotePad and save as an XML file.

I want to pull our site URLs and other info from this data.

I have been using Power BI’s Power Query Editor to help parse through this XML.  I would like to create a report based upon our URLs.  I have the following Steps that work to a point:

There are 440 rows; one for each SPO Site.  However, the results are Tables.  Each Table has 53 rows of Keys/Values (there is a screenshot of this below).  To get to the point shown above, I have had to expand several other Table columns.  Those were easy and straight forward.  But expanding this SiteData column gives me a data set of 440 x 53 = 23,320 rows.  No!!!  If I transpose that result instead, I get one row with 440 columns.  No!!! 

Ultimately, I want Power Query to send me 440 rows with each row having 53 columns.

Can this be done?

As an FYI, each Table looks like this:

[wdx223_Daniel · Accepted solution]

NewStep=Table.Combine(Table.TransformColumns(PreviousStepName,{"SiteData",each Table.FirstN(Table.PromoteHeaders(Table.Transpose(_)),1)})[SiteData])

this will give you a table with 440 rows and 53 columns, but each value will be a table.

[shaase4]

Thank you very much wdx223_Daniel! That worked!  For others that may have this same situation, here is a portion of my cleaned up code. It looks a touch better in the Advanced Editor.  I did not include code after Step13 since that was used to do easy operations like opening up the table, removing/renaming columns, etc.

let
Source = Xml.Tables(File.Contents("C:\Users\XXXXX\XXXXX\XXXXX.xml")),
Table = Source{1}[Table],
Step01 = Table.TransformColumnTypes(Table,{{"QueryId", type text}}),
Step02 = Table.RemoveColumns(Step01,{"http://schemas.microsoft.com/ado/2007/08/dataservices/metadata", "SpecialTermResults", "CustomResults", "QueryId", "QueryRuleId", "RefinementResults"}),
Step03 = Table.ExpandTableColumn(Step02, "RelevantResults", {"GroupTemplateId", "ItemTemplateId", "Properties", "ResultTitle", "ResultTitleUrl", "RowCount", "Table", "TotalRows", "TotalRowsIncludingDuplicates", "http://schemas.microsoft.com/ado/2007/08/dataservices/metadata"}, {"RelevantResults.GroupTemplateId", "RelevantResults.ItemTemplateId", "RelevantResults.Properties", "RelevantResults.ResultTitle", "RelevantResults.ResultTitleUrl", "RelevantResults.RowCount", "RelevantResults.Table", "RelevantResults.TotalRows", "RelevantResults.TotalRowsIncludingDuplicates", "RelevantResults.http://schemas.microsoft.com/ado/2007/08/dataservices/metadata"}),
Step04 = Table.RemoveColumns(Step03,{"RelevantResults.TotalRowsIncludingDuplicates", "RelevantResults.http://schemas.microsoft.com/ado/2007/08/dataservices/metadata"}),
Step05 = Table.ExpandTableColumn(Step04, "RelevantResults.TotalRows", {"Element:Text"}, {"RelevantResults.TotalRows.Element:Text"}),
Step06 = Table.RemoveColumns(Step05,{"RelevantResults.GroupTemplateId", "RelevantResults.ItemTemplateId", "RelevantResults.Properties", "RelevantResults.ResultTitle", "RelevantResults.ResultTitleUrl", "RelevantResults.RowCount"}),
Step07 = Table.ExpandTableColumn(Step06, "RelevantResults.Table", {"Rows"}, {"RelevantResults.Table.Rows"}),
Step08 = Table.ExpandTableColumn(Step07, "RelevantResults.Table.Rows", {"element"}, {"RelevantResults.Table.Rows.element"}),
Step09 = Table.ExpandTableColumn(Step08, "RelevantResults.Table.Rows.element", {"Cells"}, {"RelevantResults.Table.Rows.element.Cells"}),
Step10 = Table.RemoveColumns(Step09,{"RelevantResults.TotalRows.Element:Text"}),
Step11 = Table.ExpandTableColumn(Step10, "RelevantResults.Table.Rows.element.Cells", {"element"}, {"RelevantResults.Table.Rows.element.Cells.element"}),
Step12 = Table.RenameColumns(Step11,{{"RelevantResults.Table.Rows.element.Cells.element", "SiteData"}}),
Step13 = Table.Combine
(
Table.TransformColumns
(
Step12,
{
"SiteData",
each Table.FirstN
(
Table.PromoteHeaders
(
Table.Transpose(_)
), 1
)
}
)[SiteData]
),
XXXXX