Forum Discussion
Solved
Folder Data Source and Pivot
Hi, I have used the folder data source. In the latest version of PBI Desktop it automates most of the steps. My code is below. I have an issue where I end up with duplicate rows and the pivot fai...
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 :-)
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 DataRaw
syntax might not be 100% correct, so pls come back with error-message if it fails :-)