Forum Discussion
Power Query – How to expand and transpose a column table.
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:
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.
2 Replies
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.
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
