Dynamic Lines

[Anonymous]

Hi guys, i need your help.

I have a table and i need to generate 5 new rows depended on a specific key. As an exaple please find the below table.

Object	Field	Value
A	ACTVT	01
A	ACTVT	02
A	ACTVT	03
A	ACTVT	08
A	ACTVT	09
A	DIS	02
B	ACTVT	01
B	ACTVT	02
B	ACTVT	*
B	ACTVT	04
C	DIS	03
C	ACTVT	08
C	ACTVT	09
C	ACTVT	*
C	ACTVT	77

For each object + field when the value=* i want to generate 5 new lines, with the same object and field with specific values for each line (01, 02, 03, 04, 05). So the solution could be the table below 

Object Field Value

A	ACTVT	01
A	ACTVT	02
A	ACTVT	03
A	ACTVT	08
A	ACTVT	09
A	DIS	02
B	ACTVT	01
B	ACTVT	02
B	ACTVT	*
B	ACTVT	01
B	ACTVT	02
B	ACTVT	03
B	ACTVT	04
B	ACTVT	05
C	DIS	03
C	ACTVT	08
C	ACTVT	09
C	ACTVT	*
C	ACTVT	01
C	ACTVT	02
C	ACTVT	03
C	ACTVT	04
C	ACTVT	05
C	ACTVT	77

(New lines with red).

Could you please help me.

Thanks in advance

[v-angzheng-msft]

Hi, @Anonymous 

Try to create a new calculation table as follows:

Table 2 = 
var _targetTable=FILTER(ALL('Table'),[ValueOfTable]="*")
var _generate5=GENERATESERIES(1,5,1)
var _newTable=GENERATE(_targetTable,_generate5)
var _selectColumns=SELECTCOLUMNS(_newTable,"Object",[Object]," Field",[ Field],"ValueOfTable",FORMAT([Value],"0#"))
var _Union=UNION(_selectColumns,'Table')

return _Union

Result:

Please refer to the attachment below for details.

Hope this helps.

Best Regards,
Community Support Team _ Zeon Zheng

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

[serpiva64 · Accepted solution]

Hi, 

To obtain this (similar to your but without rows with *; if you need also rows with * it changes a little):

you can:

- create a table like this by entering data:

- merge this new table with the previous (join kind full outer):

- expand data

- replace * with null

- and finally merge the two columns

If this post is useful to help you to solve your issue consider giving the post a thumbs up 

and accepting it as a solution !

[jennratten]

Hello - this can be done with Power Query but I am wondering how this would be used.  Depending on the use case there may be better ways to solve the challenge.