M language in Query Editor - linking tables and adding rows

[ajay-sf]

Hi

I am fairly new to use of M language - so far, i managed to do what i need in Power BI, but now have a challenge, which i believe needs M language ... so appreciate your patience and help to direct me on how to achieve below ...

i have two tables:

SCORES - Parent Table   and     SCORES - Detail Table

The format and example data is as follows:

SCORES - PARENT

Table Key              Participants

1-1/1/2017           21

8-1/2/2017           53

23-1/3/2017         9

1-11/11/2017       62

SCORES - DETAIL

Table Key            Element Id      Score

1-1/1/2017          1                    3.6

1-1/1/2017          2                    3.4

1-1/1/2017          3                    3.05

1-1/1/2017          4                    2.95

8-1/2/2017          1                    2.7

8-1/2/2017          2                    1.6

8-1/2/2017          3                    3.3

8-1/12/2017        4                    2.4

23-1/3/2017        1                    3.3

23-1/3/2017        2                    1.7

23-1/3/2017        3                    2.6

23-1/3/2017        4                    3.6

1-11/11/2017      1                    1.6

1-11/11/2017      2                    2.6

1-11/11/2017      3                    3.6

1-11/11/2017      4                    4.6

I want to create a new column in the Scores-Parent Table, which using the Table Key, sums up the scores for each "Table Key".

Hence, i would expect to create a table like this (please note how i do the sum - the element 4 has 5 minus the score)...

Table Key              Participants      Sum of Score

1-1/1/2017           21                    12.1                                        from  (3.6+3.4+3.05+(5-2.95))

8-1/2/2017           53                    10.2                                        from  (2.7+1.6+3.3+(5-2.4))

23-1/3/2017         9                      9.0                                          from  (3.3+1.7+2.6+(5-3.6))

1-11/11/2017       62                    8.2                                          from (1.6+2.6+3.6+(5-4.6))

Can you please propose how i could do that in M language in Power BI Query.

Many thanks.Ajay

[Eric_Zhang · Accepted solution]

@ajay-sf

In this case, I think you shall create a measure in DAX, instead of using Power Query.

You create a proper relationship between those two tables

Then create a measure as

sum of score =
VAR tem_tbl =
    ADDCOLUMNS (
        'SCORES - DETAIL',
        "actual score", IF (
            'SCORES - DETAIL'[Element Id] = 4,
            5 - 'SCORES - DETAIL'[Score],
            'SCORES - DETAIL'[Score]
        )
    )
RETURN
    SUMX ( tem_tbl, [actual score] )

Or you simply create a calculated column as

calculated column = if('SCORES - DETAIL'[Element Id]=4,5-'SCORES - DETAIL'[Score],'SCORES - DETAIL'[Score])

See more in the attached pbix file.