Forum Discussion
Folder Data Source and Pivot
Hi Garry,
yes, looking good in general. You bind it in like this (and add 2 additional steps):
... your query up til here so far ...
#"Changed Type1" = Table.TransformColumnTypes(#"Renamed Columns",{{"Category", type text}}), // #"Added Index" = Table.AddIndexColumn(#"Changed Type1", "Index", 1, 1), Group = Table.Group(#"Changed Type1", {"Source.Name"}, {{"All", each Table.AddIndexColumn(_, "NestedIndex",1,1), type table}}), Remove = Table.RemoveColumns(#"Grouped Rows",{"Source.Name"}) Expand = Table.ExpandTableColumn(Remove, "All", Table.ColumnNames(Remove[All]{0}), Table.ColumnNames(Remove[All]{0})) #"Pivoted Column" = Table.Pivot(Expand, List.Distinct(Expand[Category]), "Category", "Column1"), DataRaw = #"Pivoted Column"{0}[DataRaw] in DataRawsyntax might not be 100% correct, so pls come back with error-message if it fails :-)
So you need a nested index. See how this is done here: https://community.powerbi.com/t5/Desktop/Adding-conditional-unique-index-column-based-on-several/td-p/110688
You just need the 2n step "Grouped Rows" and then expand. (The TelIndex and HasMatches are some specific steps for the other solution there)
Hi Imke,
Thanks for your help so far. However would you please help me with the Table.Group command?
Based on my code that I posted above, is my code here correct? Also which line should I put it on?
#"Grouped Rows" = Table.Group(Source, {"Source.Name"}, {{"All", each Table.AddIndexColumn(_, "NestedIndex",1,1), type table}}),
Hi Garry,
yes, looking good in general. You bind it in like this (and add 2 additional steps):
... your query up til here so far ...
#"Changed Type1" = Table.TransformColumnTypes(#"Renamed Columns",{{"Category", type text}}), // #"Added Index" = Table.AddIndexColumn(#"Changed Type1", "Index", 1, 1), Group = Table.Group(#"Changed Type1", {"Source.Name"}, {{"All", each Table.AddIndexColumn(_, "NestedIndex",1,1), type table}}), Remove = Table.RemoveColumns(#"Grouped Rows",{"Source.Name"}) Expand = Table.ExpandTableColumn(Remove, "All", Table.ColumnNames(Remove[All]{0}), Table.ColumnNames(Remove[All]{0})) #"Pivoted Column" = Table.Pivot(Expand, List.Distinct(Expand[Category]), "Category", "Column1"), DataRaw = #"Pivoted Column"{0}[DataRaw] in DataRawsyntax might not be 100% correct, so pls come back with error-message if it fails :-)