Extracting from Column - Better way?

[gheyman]

I am trying to pull the first character and then the first character after a delimitator - then concatenate those two. Is there a better more efficient way then doing all these steps? This is Power Query

let
Source = CostSourceData,
#"Removed Other Columns" = Table.SelectColumns(Source,{"ID", "Vendor", "[MF] Created by"}),
#"Duplicated Column" = Table.DuplicateColumn(#"Removed Other Columns", "[MF] Created by", "[MF] Created by - Copy"),
#"Inserted First Characters" = Table.AddColumn(#"Duplicated Column", "First Characters", each Text.Start([#"[MF] Created by - Copy"], 1), type text),
#"Inserted Text After Delimiter" = Table.AddColumn(#"Inserted First Characters", "Text After Delimiter", each Text.AfterDelimiter([#"[MF] Created by - Copy"], "."), type text),
#"Inserted First Characters1" = Table.AddColumn(#"Inserted Text After Delimiter", "First Characters.1", each Text.Start([Text After Delimiter], 1), type text),
#"Added Custom1" = Table.AddColumn(#"Inserted First Characters1", "UserID", each [First Characters]&[First Characters.1]),
#"Added Custom" = Table.AddColumn(#"Added Custom1", "File Name", each [ID] &"-"&[Vendor]&"-"&[UserID]),
#"Removed Other Columns1" = Table.SelectColumns(#"Added Custom",{"File Name"}),
#"Removed Duplicates" = Table.Distinct(#"Removed Other Columns1")
in
#"Removed Duplicates"
#"Removed Duplicates"

 

[RoryA]

You could do something like:

let
Source = CostSourceData,
#"Removed Other Columns" = Table.SelectColumns(Source,{"ID", "Vendor", "[MF] Created by"}),
GetInitials = Table.AddColumn(#"Removed Other Columns", "Initials", each Text.Start([#"[MF] Created by"], 1) & Text.Start(Text.AfterDelimiter([#"[MF] Created by"], "."), 1), type text),
#"Added Custom" = Table.AddColumn(GetInitials, "File Name", each [ID] &"-"&[Vendor]&"-"&[Initials]),
#"Removed Other Columns1" = Table.SelectColumns(#"Added Custom",{"File Name"}),
#"Removed Duplicates" = Table.Distinct(#"Removed Other Columns1")
in
#"Removed Duplicates"

 

[JGordon11]

Another way:

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    UserIDs = List.Transform(Source[#"[MF] Created by"], each Text.Start(_,1) & Text.Start(Text.AfterDelimiter(_, "-"),1)),
    FileNames = List.Distinct(List.Transform(List.Zip({Source[ID],Source[Vendor],UserIDs}),each Text.Combine(_,"-"))),
    Result = Table.FromColumns({FileNames}, {"File Name"})
in
    Result

Book1
	A	B	C	D	E	F	G	H	I	J	K
1	ID	Vendor	[MF] Created by	Col1	Col2	Col3	Col4	Col5		File Name
2	A	V1	MMMMM-AAAA	18	13	14	7	22		A-V1-MA
3	B	V2	NNNNN-BBB	16	1	23	25	12		B-V2-NB
4	C	V3	OOO-CCCC	19	6	20	10	5		C-V3-OC
5	D	V4	PP-DD	24	8	15	21	3		D-V4-PD
6	E	V5	QQQ-E	17	4	9	2	11		E-V5-QE
7	A	V1	MMMMM-AAAA	10	5	20	33	4
8
Sheet1

 

[gheyman]

Thank you both. Each works. Very much appreciate the help and training