Custom column question

[Sniklfritz]

Hello all,
I am stuck on something im not sure if its even possible...

I have 2 connection only queries in excel. The first is Cost Center and the 2nd is Flights.

In the Flights Query I am trying to reference the cost center in a new custom column and add the cost center number into the flight query. However i cant find how to actually reference the query?? E.g. CostCenter[Column D].

Merge is not an option for a few reasons...

Thx

 

[jdellasala]

While it might be possible to create a custom column to actually pull the data from the Cost Center table into the Flights table, the easy way is to merge the Cost table with (into) the Flights table. This can be done within the Flights query, or a new query can be created.
In the code below, the CostCtr table has a column of Cost Center numbers, and a column of their names. The Flights table has Flight Numbers and Cost Center numbers. This creates a new query from the Flights table merging the CostCtr table's Cost Center's rows that match the Fligth's Cost Center:

let
    Source = Table.NestedJoin(tblFlights, {"Cost Ctr"}, tblCostCtr, {"Cost Center"}, "tblCostCtr", JoinKind.LeftOuter),
    ExpandedtblCostCtr = Table.ExpandTableColumn(Source, "tblCostCtr", {"Name"}, {"tblCostCtr.Name"})
in
    ExpandedtblCostCtr

The Merge drop down on the Home tab of the PQ Editor allows you to create the merge within the Flights table or create a new Query/Table as shown above.
It wasn't needed here, but it's helpful if you update your profile to indicate which version of Excel and OS you're using. Also, using XL2BB to post sample data is preferable. This is what that looks like:

Book1
	A	B	C	D	E	F	G	H	I
1	tblCostCtr			tblFlights			Result
2	Cost Center	Name		Flight	Cost Ctr		Flight	Cost Ctr	tblCostCtr.Name
3	101	Airplane		3030	103		3030	109	Doubtful Advertisement
4	102	Jade Authority		3031	108		3034	109	Doubtful Advertisement
5	103	Sandy Brown Earth		3032	104		3035	109	Doubtful Advertisement
6	104	Hobby		3033	110		3031	101	Airplane
7	105	Nondescript Science		3034	104		3032	101	Airplane
8	106	Late Wheel		3035	103		3039	101	Airplane
9	107	Science		3036	107		3036	103	Sandy Brown Earth
10	108	Fight		3037	101		3033	105	Nondescript Science
11	109	Doubtful Advertisement		3038	103		3037	105	Nondescript Science
12	110	Memory		3039	107		3038	108	Fight
Sheet1

 

[GraH]

Merge is not an option for a few reasons...

I'm wondering why you say this?

 

[jdellasala]

I'm wondering why you say this?

I agree with your sentiment, but apologize that I missed that!

 

[jdellasala]

Sorry I missed your note, but would like to know why merging isn't an option!
That said, here's the query needed based on the tables in my original post:

let
    Source = Excel.CurrentWorkbook(){[Name="tblFlights"]}[Content],
    ChangedType = Table.TransformColumnTypes(Source,{{"Flight", Int64.Type}, {"Cost Ctr", Int64.Type}}),
    AddedCustom = Table.AddColumn(ChangedType, "Cost Ctr Name", each tblCostCtr[Name]{List.PositionOf(tblCostCtr[Cost Center],[Cost Ctr])}, type text)
in
    AddedCustom

The AddedCustom line basically looks up the Index (from 0) of the FIRST Match in tblCostCtr and returns that name. Worth noting that in either solution, if the Cost Center number isn't a UNIQUE list of values, a correct result is not insured. In the Merge you'd get all the matching rows and create the number of matching rows for each item looked up. In this solution you'd only get the first matching record which would be totally dependent on the sort of the Cost Center table. If there are multiple values for the Cost Center Number, neither solution is really correct. There should be a dimension table with unique values for a field to use for a lookup in either case.

 

[Sniklfritz]

There was no common values to merge against

 

[Sniklfritz]

I found a solution where i match characters and if a certain amount match it replaces

 

[GraH]

Match characters? That sounds like a fuzzy merge.
Can't you share your solution, @Sniklfritz ? Always nice to have it available for learning and knowledge sharing purposes.