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

Loop through rows to create new column using query

Hello,

 

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:

 

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

This is GetCustomerDetails

 

let
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}})
in
    #"Changed Type"

This is GetCustomerList

 

let
pcToken = GetPartnerCenterToken(OMITTED), 

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

Any help would be appreciated!

 

 

1 REPLY 1
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: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

If you try other Join kind

5.JPG

https://docs.microsoft.com/en-us/powerquery-m/table-join

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

 

Best Regards,
Lin

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

Announcements
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)