Collect/Aggregate Values

[Anonymous]

i have a table with source and destination, 

i would like to have an aggregated values that will collect all location that will pass on... 

let

SourceTable = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],

FindDestinations = (source as text, destinationList as list) =>
let
FilteredRows = Table.SelectRows(SourceTable, each [Destination] = source),
RecursiveResults =
if Table.RowCount(FilteredRows) > 0 then
List.Accumulate(
Table.Column(FilteredRows, "Source"),
{},
(state, current) => List.Combine({state, FindDestinations(current, destinationList)})
)
else
{source}
in
List.Combine({destinationList, RecursiveResults}),

AggregatedResults = Table.AddColumn(
SourceTable,
"AggregatedResults",
each Text.Combine(List.Reverse(FindDestinations([Destination], {})), ",")
)
in
AggregatedResults

but still having an error results.. 

Below Result should be the output

Source	Destination	AggregatedResults
Site1	Site2	Site2,Site1
Site2	Site3	Site3,Site2,Site1
Site3	Site4	Site4,Site3,Site2,Site1
Site4	Site5	Site5,Site4,Site3,Site2,Site1,Site7,Site6,Site8
Site6	Site7	Site7,Site6
Site7	Site5	Site5,Site7,Site6
Site8	Site5	Site5,Site8

[SamInogic]

Hi  @Anonymous 
It seems like you're trying to create a custom function in Power Query (M Language) to recursively find destinations for each source in your table. Your approach is on the right track, but the error might be due to incorrect referencing or data types.

You can try below changed code,

let
SourceTable = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],

FindDestinations = (source as text, destinationList as list) =>
let
FilteredRows = Table.SelectRows(SourceTable, each [Destination] = source),
RecursiveResults =
if Table.RowCount(FilteredRows) > 0 then
List.Accumulate(
Table.Column(FilteredRows, "Source"),
{},
(state, current) => List.Combine({state, FindDestinations(current, destinationList)})
)
else
{source}
in
List.Combine({destinationList, RecursiveResults}),

AggregatedResults = Table.AddColumn(
SourceTable,
"AggregatedResults",
each Text.Combine(List.Reverse(FindDestinations([Destination], {})), ",")
)
in
AggregatedResults

If you're still encountering errors, please provide more details about the error message you're receiving, and I can assist you further.

Thanks!

Inogic Professional Service Division

An expert technical extension for your techno-functional business needs

Power Platform/Dynamics 365 CRM

Drop an email at crm@inogic.com

Service:  http://www.inogic.com/services/ 

Power Platform/Dynamics 365 CRM Tips and Tricks:  http://www.inogic.com/blog/

[Anonymous]

i have copy your code, but still having some Error output on the AggregatedResults column

The Result should be like what i have on the Result Column which i done it manually.

Its like a tree map, but collected the nodes where it was connected.

if i click the error, it show like below

[Anonymous]

Hi @Anonymous 

You can try the following code,please make sure the column name is the same as the data you have provided.

let
   SourceTable = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Added Index" = Table.AddIndexColumn(SourceTable, "Index", 1, 1, Int64.Type),
    #"Added Custom" = Table.AddColumn(#"Added Index", "Custom", each let 
 c=[Destination],p=[Source],mytable=#"Added Index",pc="Source",cc="Destination" 
  in
let mylist={c} & List.Generate(()=>[x=0,y=p,w=1],each [w] > 0,each [z=[y], 
x=Table.Column(Table.SelectRows(mytable,each Record.Field(_,cc)=z),pc),y=x{0},w=List.Count(x)
],
each [y])
        in
Text.Combine(List.RemoveItems(
List.Transform(mylist,each Text.From(_)),{null,""}),",")),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "AggregatedResults", each let a=List.Min(Table.SelectRows(#"Added Custom",(x)=>x[Destination]=[Destination])[Index]),
b=if [Index]=a then  Text.Combine(List.Distinct(
    Text.Split(
Text.Combine(Table.SelectRows(#"Added Custom",(x)=>x[Destination]=[Destination] and x[Index]>=[Index])[Custom],","),",")),",") else [Custom] 
in b),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"Custom", "Index"})
in
    #"Removed Columns"

Output

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

@Anonymous thanks for the support, but this does't give a right output.

[Anonymous]

Hi @Anonymous 

Can you provide some sample picture of the result that you put the code I have offered?

Best Regards!

Yolo Zhu

[Anonymous]

Hi @Anonymous 

Or you can refer to the following link.

https://community.fabric.microsoft.com/t5/Quick-Measures-Gallery/DAX-s-PATH-function-equivalent-Custom-Column-in-Power-Query/m-p/800386

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.