cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

Combining Multiple Records to One via a collection

Hi,

 

Struggling to combined multiple records from a source table to a single record.  

 

Example below, if i could only get my current table to replicate the value for each Step for the Metric i could GroupBy the Metric and each Step which will give me my two records but I cannot get it do so as each Metric & Step also has a null so will remain with 10 records!   Help would be very much appreciative.

1.PNG

Initial & Target Tables are just a basic collections table manually built to demonstrate my starting  and end points.

 

Initial Table code:

ClearCollect(InitialTable,
{Metric: "AHT(AverageHandlingTime)", Step: "1", Target: "600"},
{Metric: "AHT(AverageHandlingTime)", Step: "2", Target: "575"},
{Metric: "AHT(AverageHandlingTime)", Step: "3", Target: "550"},
{Metric: "AHT(AverageHandlingTime)", Step: "4", Target: "525"},
{Metric: "AHT(AverageHandlingTime)", Step: "5", Target: "500"},
{Metric: "CSAT(CustomerSatisfaction)", Step: "1", Target: "0.75"},
{Metric: "CSAT(CustomerSatisfaction)", Step: "2", Target: "0.8"},
{Metric: "CSAT(CustomerSatisfaction)", Step: "3", Target: "0.85"},
{Metric: "CSAT(CustomerSatisfaction)", Step: "4", Target: "0.9"},
{Metric: "CSAT(CustomerSatisfaction)", Step: "5", Target: "0.95"})

 

Target Table Code:

ClearCollect(TargetTable,
{Metric: "AHT(AverageHandlingTime)", Step1: "600", Step2: "575", Step3: "550", Step4: "525", Step5: "500"},
{Metric: "CSAT(CustomerSatisfaction)", Step1: "0.75", Step2: "0.8", Step3: "0.85", Step4: "0.9", Step5: "0.95"})

 

Current Table Code (thus far):

ClearCollect(CurrentTable,AddColumns(InitialTable,
"Step1",LookUp(GroupBy(InitialTable,"Metric","Other"),Step="1",Target),
"Step2",LookUp(GroupBy(InitialTable,"Metric","Other"),Step="2",Target),
"Step3",LookUp(GroupBy(InitialTable,"Metric","Other"),Step="3",Target),
"Step4",LookUp(GroupBy(InitialTable,"Metric","Other"),Step="4",Target),
"Step5",LookUp(GroupBy(InitialTable,"Metric","Other"),Step="5",Target)))

 

Thank you in advance Smiley Happy

1 ACCEPTED SOLUTION

Accepted Solutions
CarlosFigueira
Power Apps
Power Apps

The initial table has 10 rows, so if you call AddColumns to it, it will continue having the 10 rows. What you need to do first is to get all Distinct values of the Metric from that table, and at that point you can use your logic. The following expression should give you the target table:

ClearCollect(
    TargetTable,
    RenameColumns(
        AddColumns(
            Distinct(InitialTable, Metric),
            "Step1", LookUp(InitialTable, Metric = Result And Step = "1", Target),
            "Step2", LookUp(InitialTable, Metric = Result And Step = "2", Target),
            "Step3", LookUp(InitialTable, Metric = Result And Step = "3", Target),
            "Step4", LookUp(InitialTable, Metric = Result And Step = "4", Target),
            "Step5", LookUp(InitialTable, Metric = Result And Step = "5", Target)),
        "Result", "Metric"))

The Distinct function returns a table with the unique values for the column with a column called 'Result'. After adding the columns for that table, we can then rename that column to what we want.

View solution in original post

1 REPLY 1
CarlosFigueira
Power Apps
Power Apps

The initial table has 10 rows, so if you call AddColumns to it, it will continue having the 10 rows. What you need to do first is to get all Distinct values of the Metric from that table, and at that point you can use your logic. The following expression should give you the target table:

ClearCollect(
    TargetTable,
    RenameColumns(
        AddColumns(
            Distinct(InitialTable, Metric),
            "Step1", LookUp(InitialTable, Metric = Result And Step = "1", Target),
            "Step2", LookUp(InitialTable, Metric = Result And Step = "2", Target),
            "Step3", LookUp(InitialTable, Metric = Result And Step = "3", Target),
            "Step4", LookUp(InitialTable, Metric = Result And Step = "4", Target),
            "Step5", LookUp(InitialTable, Metric = Result And Step = "5", Target)),
        "Result", "Metric"))

The Distinct function returns a table with the unique values for the column with a column called 'Result'. After adding the columns for that table, we can then rename that column to what we want.

View solution in original post

Helpful resources

Announcements
PA_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Power Query PA Forum 768x460.png

Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps

Carousel 2021 Release Wave 2 Plan 768x460.jpg

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

R2 (Green) 768 x 460px.png

Microsoft Dynamics 365 & Power Platform User Professionals

DynamicsCon is a FREE, 4 half-day virtual learning experience for 11,000+ Microsoft Business Application users and professionals.

Users online (4,529)