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)
thank you I will try your solution now.
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 :-)
