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