Transform rows into one column

[req77]

Hello,

I have a table with the following structure:

Year_Month	Type	Value
2023.01	Production	5
2023.01	Production_Target	7
2023.01	Compliance	8
2023.01	Compliance_Target	8
2023.02	Production	10
2023.02	Production_Target	12
2023.02	Compliance	4
2023.02	Compliance_Target	6
2023.03	Production	15
2023.03	Production_Target	13
2023.03	Compliance	11
2023.03	Compliance_Target	12

Rows where the Type column has the value "_Target" indicate that they are rows with target values ​​for the respective Indicator Type (for example, the type "Production" is the actual production value for each month and the "Production-Target" is the expected value for each month). I want to create charts for each Type that show the actual value for each month and a goal line (which shows the "_Target" values ​​for each month), but I'm not able to turn this table into a better way.

My expected table result would be this:

Year_Month	Type	Value	Objective
2023.01	Production	5	7
2023.02	Production	10	12
2023.03	Production	15	13
2023.01	Compliance	8	8
2023.02	Compliance	4	6
2023.03	Compliance	11	12

[v-yangliu-msft · Accepted solution]

Hi  @req77 ,

Here are the steps you can follow：

1. In Power Query -- Add Column – Index Column – From 1.

2. Create calculated column.

Group =
MINX(
    FILTER(ALL('Table'),'Table'[Year_Month]=EARLIER('Table'[Year_Month])),[Index])

Type_Group =
IF(
    CONTAINSSTRING('Table'[Type],"Production_Target")=TRUE()
    ||
    CONTAINSSTRING('Table'[Type],"Compliance_Target")=TRUE()
    ,0,1)

Sum =
SUMX(
    FILTER(ALL('Table'),
    'Table'[Index]<=EARLIER('Table'[Index])),[Type_Group])

Rank =
RANKX(
    FILTER(ALL('Table'),
    'Table'[Group]=EARLIER('Table'[Group])&&'Table'[Sum]=EARLIER('Table'[Sum])),[Index],,ASC)

Objective =
IF(
    [Rank]=
    MINX(
        FILTER(ALL('Table'),'Table'[Sum]=EARLIER('Table'[Sum])),[Rank]),
     SUMX(
         FILTER(ALL('Table'),
         'Table'[Sum]=EARLIER('Table'[Sum])&&'Table'[Rank]=
         MAXX(
             FILTER(
                 ALL('Table'),'Table'[Sum]=EARLIER('Table'[Sum])),[Rank])),[Value]
))

3. Create calculated table.

Table 2 =
var _table=FILTER('Table',[Rank]=1)
return
SUMMARIZE(
    _table,[Year_Month],[Type],[Value],[Objective])

4. Result:

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly