Power Query - Filling down a Block of Data

[scpournara]

I have a data set and I am trying to create a new column where I can repeat the MFG column for each Name. If I simply Fill Don in Column "MFG" it get CRC fro all the records. I want to build a Column where to get thRow 1 thru 16 to repeat all the way down. Thanks.

 

[JGordon11]

If you have Names in Table1 and MFG in Table2 like this:

Book1
	A	B	C	D
1
2	Name		MFG
3	Name1		Southwire
4	Name2		Southwire TCAS (Tools, Components & Assembled Solutions)
5	Name3		Heritage
6	Name4		Crouse Hinds
7			Power Quaity
8			Bussmann
9			Wiring Device
10			lisco
11			Hammond
12			HellermannTyton
13			Harger
14			RAB
15			FLO
16			CRC
17
18
Sheet2

the following query:

let
    NamesTable = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    MFGTable = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
    AddMFG = Table.AddColumn(NamesTable, "MFGCustom", each MFGTable),
    ExpandMFG = Table.ExpandTableColumn(AddMFG, "MFGCustom", {"MFG"}, {"MFG"})
in
    ExpandMFG

Produces this:

Book1
	A	B
1	Name	MFG
2	Name1	Southwire
3	Name1	Southwire TCAS (Tools, Components & Assembled Solutions)
4	Name1	Heritage
5	Name1	Crouse Hinds
6	Name1	Power Quaity
7	Name1	Bussmann
8	Name1	Wiring Device
9	Name1	lisco
10	Name1	Hammond
11	Name1	HellermannTyton
12	Name1	Harger
13	Name1	RAB
14	Name1	FLO
15	Name1	CRC
16	Name2	Southwire
17	Name2	Southwire TCAS (Tools, Components & Assembled Solutions)
18	Name2	Heritage
19	Name2	Crouse Hinds
20	Name2	Power Quaity
21	Name2	Bussmann
22	Name2	Wiring Device
23	Name2	lisco
24	Name2	Hammond
25	Name2	HellermannTyton
26	Name2	Harger
27	Name2	RAB
28	Name2	FLO
29	Name2	CRC
30	Name3	Southwire
31	Name3	Southwire TCAS (Tools, Components & Assembled Solutions)
32	Name3	Heritage
33	Name3	Crouse Hinds
34	Name3	Power Quaity
35	Name3	Bussmann
36	Name3	Wiring Device
37	Name3	lisco
38	Name3	Hammond
39	Name3	HellermannTyton
40	Name3	Harger
41	Name3	RAB
42	Name3	FLO
43	Name3	CRC
44	Name4	Southwire
45	Name4	Southwire TCAS (Tools, Components & Assembled Solutions)
46	Name4	Heritage
47	Name4	Crouse Hinds
48	Name4	Power Quaity
49	Name4	Bussmann
50	Name4	Wiring Device
51	Name4	lisco
52	Name4	Hammond
53	Name4	HellermannTyton
54	Name4	Harger
55	Name4	RAB
56	Name4	FLO
57	Name4	CRC
58
59
Merge

 

[scpournara]

ExpandMFG

Thanks. I did create a Table for the Names and a Table for the MFG. I copied your formula and tried to insert it through the Advance Editor on the Power Query. Below is the current code. How do I insert to get the results. Thanks for you help.

let
Source = Folder.Files("G:\Shared drives\30-ElectroRep Management\Human Resources\Time Allocation\fdata"),
#"Filtered Hidden Files1" = Table.SelectRows(Source, each [Attributes]?[Hidden]? <> true),
#"Invoke Custom Function1" = Table.AddColumn(#"Filtered Hidden Files1", "Transform File", each #"Transform File"([Content])),
#"Renamed Columns1" = Table.RenameColumns(#"Invoke Custom Function1", {"Name", "Source.Name"}),
#"Removed Other Columns1" = Table.SelectColumns(#"Renamed Columns1", {"Source.Name", "Transform File"}),
#"Expanded Table Column1" = Table.ExpandTableColumn(#"Removed Other Columns1", "Transform File", Table.ColumnNames(#"Transform File"(#"Sample File"))),
#"Changed Type" = Table.TransformColumnTypes(#"Expanded Table Column1",{{"Estimated % of Time Last 12 Months", Percentage.Type}, {"Anticipated % of Time Next 12 Months", Percentage.Type}}),
#"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"Column5", "Column6", "Column7"}),
#"Filtered Rows" = Table.SelectRows(#"Removed Columns", each ([Name] <> null)),
#"Renamed Columns" = Table.RenameColumns(#"Filtered Rows",{{"Erica Ball", "MFG"}}),
#"Changed Type1" = Table.TransformColumnTypes(#"Renamed Columns",{{"MFG", type text}})
in
#"Changed Type1"