Power Query Changed Table

[arash870]

How to make the yellow and red colored houses on the surface in the Power Query as a column I manually set. Without breaking the table - thank you

Book1
	A	B	C	D	E	F	G	H	I	J
1	Amount of inventory	Number of inventory	selling price	sales-number	name branch	cod		row	name	cod
2	lozaten tab					56511001	product
3	167,736,000	1205		0	office center		1	1	lozaten tab	56511001
4	3,619,200	26	1,281,343,800	8074	tehran		10	2	lozaten tab	56511001
5	6,820,800	49	178,696,200	1126	qazvin		14	3	lozaten tab	56511001
6	6,820,800	49	109,661,700	691	qom		16	4	lozaten tab	56511001
7	17,817,600	128	43,959,900	277	kerman		18	5	lozaten tab	56511001
8	1,252,800	9	223,925,700	1411	mazandaran		20	6	lozaten tab	56511001
9		0	81,571,800	514	oromye		22	7	lozaten tab	56511001
10		0	151,558,500	955	khorasan		24	8	lozaten tab	56511001
11	556,800	4	104,107,200	656	kermanshah		26	9	lozaten tab	56511001
12	5,011,200	36	33,168,300	209	yazd		28	10	lozaten tab	56511001
13		0	184,092,000	1160	isfehan		30	11	lozaten tab	56511001
14	696,000	5	130,134,000	820	qilan		34	12	lozaten tab	56511001
15	12,528,000	90	127,753,500	805	tabriz		36	13	lozaten tab	56511001
16	2,784,000	20	31,740,000	200	kord		40	14	lozaten tab	56511001
17	7,238,400	52	158,382,600	998	khozestan		42	15	lozaten tab	56511001
18	5,568,000	40	57,132,000	360	hamedan		44	16	lozaten tab	56511001
19		0	58,877,700	371	ardebil		46	17	lozaten tab	56511001
20	238,449,600	1713	2,956,104,900	18627	56511001 جمع کالای				lozaten tab	56511001
21	captppril					56511004	:کالا		lozaten tab	56511001
22	115,900,200	1651		0	office center		1	1	captppril	56511004
23	9,196,200	131	67,920,000	849	tehran		10	2	captppril	56511004
24	7,020,000	100	1,600,000	20	qazvin		14	3	captppril	56511004
25	7,722,000	110	8,800,000	110	qom		16	4	captppril	56511004
26	6,318,000	90	1,600,000	20	kerman		18	5	captppril	56511004
27	13,408,200	191	1,520,000	19	mazandaran		20	6	captppril	56511004
28	702,000	10	8,000,000	100	oromye		22	7	captppril	56511004
29		0	9,600,000	120	kermanshah		26	8	captppril	56511004
30	3,159,000	45		0	yazd		28	9	captppril	56511004
31	10,389,600	148	160,000	2	isfehan		30	10	captppril	56511004
32	10,530,000	150		0	qilan		34	11	captppril	56511004
33	11,583,000	165	1,200,000	15	tabriz		36	12	captppril	56511004
Sheet2

 

[sandy666]

if table A1-H33 is a Query Table it is not proper table because you have mixed data type in the same column
if you want additional two columns use Conditional Columns and Fill Down
I don't know what is Source, what is Query Table what is expected result

 

[arash870]

How to do it Please explain

 

[sandy666]

to be sure:
Table A1:H33 is your source table?
re-read post#2

 

[arash870]

Yes - it's my final table - and there is an empty row between the rows of the table

 

[sandy666]

I am not sure but you can try

// Table1
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Amount of inventory", type any}, {"Number of inventory", Int64.Type}, {"selling price", Int64.Type}, {"sales-number", Int64.Type}, {"name branch", type text}, {"cod", Int64.Type}, {"Column1", type any}, {"row", Int64.Type}}),
    #"Added Conditional Column" = Table.AddColumn(#"Changed Type", "Custom", each if [Amount of inventory] = "lozaten tab" then [Amount of inventory] else if [Amount of inventory] = "captppril" then [Amount of inventory] else null),
    #"Filled Down" = Table.FillDown(#"Added Conditional Column",{"Custom"}),
    #"Duplicated Column" = Table.DuplicateColumn(#"Filled Down", "cod", "cod - Copy"),
    #"Filled Down1" = Table.FillDown(#"Duplicated Column",{"cod - Copy"})
in
    #"Filled Down1"

 

[arash870]

فا

I am not sure but you can try

// Table1
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Amount of inventory", type any}, {"Number of inventory", Int64.Type}, {"selling price", Int64.Type}, {"sales-number", Int64.Type}, {"name branch", type text}, {"cod", Int64.Type}, {"Column1", type any}, {"row", Int64.Type}}),
    #"Added Conditional Column" = Table.AddColumn(#"Changed Type", "Custom", each if [Amount of inventory] = "lozaten tab" then [Amount of inventory] else if [Amount of inventory] = "captppril" then [Amount of inventory] else null),
    #"Filled Down" = Table.FillDown(#"Added Conditional Column",{"Custom"}),
    #"Duplicated Column" = Table.DuplicateColumn(#"Filled Down", "cod", "cod - Copy"),
    #"Filled Down1" = Table.FillDown(#"Duplicated Column",{"cod - Copy"})
in
    #"Filled Down1"

View attachment 3245

thank you

 

[sandy666]

You are welcome