Showing results for 
Search instead for 
Did you mean: 
Samph Frequent Visitor
Frequent Visitor

Loop through rows to create new column using query



I have a Query (GetCustomerDetails) that retrieves customer details for a given CustomerID. It returns this as a record which contains a list field (customDomains)


I also have a Query (GetCustomerList) that retrives a list of all customers and their IDs (but it is missing the customDomains field)


I want to merge the two queries so that for each row in GetCustomerList it retrives the customDomains for the matching ID using the GetCustomerDetails query.


Currently if i merge the two, it only populates the customDomains field for the Customer ID prestipulated in GetCustomerDetails.  


I feel like I need to use a parameter of some sort to reference the ID field in GetCustomerList so that it will retrive the customDomains for each row.


This is my merged table:


    Source = Table.NestedJoin(GetCustomerList,{"id"},GetCustomerDetails,{"id"},"GetCustomerDetails",JoinKind.LeftOuter),
    #"Expanded GetCustomerDetails" = Table.ExpandTableColumn(Source, "GetCustomerDetails", {"customDomains"}, {"GetCustomerDetails.customDomains"})
    #"Expanded GetCustomerDetails"

This is GetCustomerDetails


pcToken = #"GetPartnerCenterToken (2)"(OMITTED), 

customers = GetCustomerDetail(pcToken,#"GetCustomerList"[id]{0}),
    #"Converted to Table" = Record.ToTable(customers),
    #"Transposed Table" = Table.Transpose(#"Converted to Table"),
    #"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table", [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"id", type text}, {"commerceId", type text}, {"companyProfile", type any}, {"billingProfile", type any}, {"relationshipToPartner", type text}, {"allowDelegatedAccess", type logical}, {"customDomains", type any}, {"links", type any}, {"attributes", type any}})
    #"Changed Type"

This is GetCustomerList


pcToken = GetPartnerCenterToken(OMITTED), 

customers = GetCustomers(pcToken), #"Renamed Columns" = Table.RenameColumns(customers,{{"companyName", "Company Name"}})
    #"Renamed Columns"

Any help would be appreciated!



Community Support Team
Community Support Team

Re: Loop through rows to create new column using query

hi, @Samph 

Sample data and expected output would help tremendously. Please see this post regarding How to Get Your Question Answered Quickly:

If you try other Join kind


If not your case, Please share some simple sample data and expected output.


Best Regards,

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

Helpful resources

New Kudos Given Badges Coming

New Kudos Given Badges Coming

We're rolling out new Kudos Given badges. Find out how many Kudos you've given.

Power Platform World Tour

Power Platform World Tour

Find out where you can attend!

Top Kudoed Authors (Last 30 Days)
Users online (3,567)