Create a column "looking up" from another column

[gxm204]

Hi, For this example (as shown below) I have a `year` column and I would like to create a `decade` column based on those values.

In "classic Excel" I'd probably create a lookup table. What would be the best Power Query strategy for this? Make a separate lookup table and then do a left join? Or is there a way to do it all in one?

Thanks all!

 

[sandy666]

post a link to shared file with source data and expected result not a picture

 

[Joe4]

I don't think you need a lookup at all.
Just try this formula (for an entry in cell A1):
=LEFT(A1,3)&"0-"&LEFT(A1,3)&"9:"&LEFT(A1,3) & "0's"

 

[sandy666]

@Joe4

What would be the best Power Query strategy for this?

 

[Joe4]

What would be the best Power Query strategy for this?

I apologize. I was distracted and talking to someone while I was replying and obviously did not read the question as closely as I should have.

 

[gxm204]

@Joe4 OK maybe that was a bad example to use ?‍ but I do like using `LEFT()` in this example!
Let's say for example I have a list of teams. I want to create a column based on their league, but don't have a lookup table handy, should I make one? Or is there some kind of function in PQ that would create the groupings for me? Maybe it's just an IF statement.

 

[Joe4]

Unfortunately, I do not know Power Query (sadly, my workplace won't let me download it, and I don't have that much time at home), but Sandy does, so hopefully he will be able to help you (your question seems to be pretty straightforward).

 

[alansidman]

I created a table with years and the Mcode for that file is:

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Year", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each Text.Middle([Year],2,1))
in
    #"Added Custom"

Second Table looks like below:

Book1
	A	B
2	2	1920's
3	3	1930's
4	4	1940's
5	5	1950's
6	6	1960's
7	7	1970's
8	8	1980's
9	9	1990's
10	0	2000's
11	1	2010's
Sheet3

Mcode for that table:

let
    Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}})
in
    #"Changed Type"

Mcode to merge tables:

let
    Source = Table.NestedJoin(Table1, {"Custom"}, Table2, {"Column1"}, "Table2", JoinKind.LeftOuter),
    #"Expanded Table2" = Table.ExpandTableColumn(Source, "Table2", {"Column2"}, {"Table2.Column2"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded Table2",{"Custom"})
in
    #"Removed Columns"

Result

Book1
	A	B
1	Year	Table2.Column2
2	1921	1920's
3	1922	1920's
4	1923	1920's
5	1930	1930's
6	1935	1930's
7	1936	1930's
8	1940	1940's
9	1941	1940's
10	1952	1950's
11	1953	1950's
12	1962	1960's
13	1963	1960's
14	1971	1970's
15	1972	1970's
16	1974	1970's
17	1985	1980's
18	1986	1980's
19	1995	1990's
20	1996	1990's
21	2000	2000's
22	2001	2000's
23	2005	2000's
24	2015	2010's
25	2016	2010's
26	2019	2010's
Sheet5

 

[gxm204]

That's a wealth of information, thanks so much for posting Alan.