Power Query - M

Power Query
1

Create A Delimited List In Power Query - Query Editor Tutorial - YouTube

Table of distinct Rows has an embedded table object.
To extract a particular column values for a “cell”, as a an embed list from the embedded table. this iteration is enforced using = [AllRows][Product]
The question is does the iteration happens based on the cell value in the Main table or on the embedded table object?

Can we also sum the value from the embedded table object if it is a number?

2

I can’t see the youtube right now but if you go here it will give you an idea of some of the functions you can use for summing.

SUMMARIZECOLUMNS function (DAX) - DAX | Microsoft Docs

SUMMARIZE function (DAX) - DAX | Microsoft Docs

3

Hi @Sairam,

Welcome to the Forum!

I’m not 100% clear on your requirement but I hope this will help.
Just copy the full script into a new blank query.

let
    Source = Table.FromColumns( {{ Table.FromColumns( {{"A".."E"}, {1..5}}, {"Type", "Value"}) }}, {"Nested Table"} ),
    AddSum = Table.AddColumn(Source, "Sum", each List.Sum( [Nested Table][Value] ))
in
    AddSum

with this result.

image

2 Likes
4

Thanks @Melissa for your response.
You have clarified my second question.
With regard to first question
In your example in the video when extracting products purchased by customer by adding custom column. Doest the iteration happens from the Main table(Customers - Distinct table) or from embedded table(Customers in AllRows) when applying
[AllRows][Product].
Since in DAX only X functions has row context in a calculated column. In other words is the list faciltate iteration in M. a) main table(Customers) to embeded table or b) Iteration within the embeded table.

In Create A Delimited List of Values in Power Query - Query Editor Tutorial | Enterprise DNA

5

Okay looking at the example above the calculation happens for each row in the outer table, which then performs a calculation over a list that contains the value from each row in the nested table.

Hope this helps

2 Likes
6

Thanks @Melissa
Is this true only for aggregation and accumulation operators and not for comparison operators(
outer environment take precedence over inner)

7

Hi @Sairam , welcome to the Forum! :slight_smile:

It’s great to see that your inquiry is being attended by our users and experts.

Kindly take time to answer the Enterprise DNA Forum User Experience Survey, we hope you’ll give your insights on how we can further improve the Support forum. Thanks!

8

Hi @Sairam! We noticed that your inquiry was left unsolved for quite some time now.

Looks like your inquiry was out of the experts and users’ bounds.

We strive to answer ALL inquiries in the forum. However, if you are sure that you provided all pertinent context to your concerns and read how to use the forum more effectively and still find your question unanswered, you can check out tutorials to learn this yourself as your membership also comes with relevant resources that may help you with your Power BI education.

While our users and experts do as much as reasonable to help you with your inquiries, not all concerns can be attended to especially if there are some learnings to be done. Thank you!