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 Topics Started Badges Coming

New Topics Started Badges Coming

We're releasing new versions of the badge that everyone's talking about. ;) Check your inbox for notifications.

MBAS 2020

Save the new date (and location)!

Our business applications community is growing—so we needed a different venue, resulting in a new date and location. See you there!

Difinity Conference

Difinity Conference

The largest Power BI, Power Platform, and Data conference in New Zealand

Top Solution Authors
Top Kudoed Authors (Last 30 Days)