Selecting name fo column dynamically

[prakhar]

I have two tables that look like this:
Table1
Text | Level
a   |   12
b   |   16
c   |   20

Table 2
Item | a | b | c
X  | 10  | 8   | 15
Y  |  15 | 16 | 19
Z  |  22 |  18 | 25

I need to create a new column in Table 1 such that it gives the Item for which value of a/b/c is least greater than level
So the new column in Table 1 will be will be
Y
Z
Z

[v-huizhn-msft]

Hi @prakhar,

I try to reproduce your scenario and get expected result as the following steps.

1. Select Table2[a], Table2[b],Table2[c] fields->click Unpivot columns under “Transform" on Edit Query Home page, you will get the following table.



2. Create relationship between Table1 and Table2 based on a,b,c values.



3. Create calculated columns using the formulas and get result.

Column 2 = Table2[Value]-RELATED(Table1[Level])

Column 4 = CALCULATE(MIN(Table2[Column 2]),FILTER(Table2,Table2[Attribute]=EARLIER(Table2[Attribute])),Table2[Column 2]>0)

4. Click new table under Modeling on home page using the formula.

NewTable = SELECTCOLUMNS(FILTER(Table2,Table2[Column 2]=Table2[Column 4]),"Text",Table2[Attribute],"Item",Table2[Item])

5. In table1, create a column to get expected result.

Result = LOOKUPVALUE(NewTable[Item],NewTable[Text],Table1[Text])

If you have other issues, please feel free to ask.

Best Regards,
Angelia

[prakhar]

Sorry for the late reply. My Table 2 was created using DAX, so it wasn't able to apply the queries right away. Both @v-huizhn-msft and @MarcelBeug achieve what I require but I am not able to check them until I convert my Table2 DAX to M Query.

I was able to figure out most of it but I need some help in converting some DAX expressions. I think writing query is easier than converting for my case since my DAX expressions are more complicated than they need to be.


I have a Table 3 with multiple columns.
Table 3
Date | Hour| Items|.....
11-2-2017| 15 | aaa,baa,caa
11-2-2017| 17 | baa,caa
11-3-2017| 18 | aaa,caa
....


I need to summarize the Hour column in a new table and in a new column of that table calculate the number of times aaa appears upto that hour for any date divided by the number of unique dates in the whole Table3.

Regards
Prakhar

[v-huizhn-msft]

Hi @prakhar,

You create Table2 using Table3? Could you please share your sample data for further analysis?

Best Regards,
Angelia

[prakhar]

Hi @v-huizhn-msft,

I had not created it directly. It involved more steps. I just posted the step I needed help with. I posted it in a separate post since its answer did not need the first two tables. I got the answer from that post and the solution by @MarcelBeug worked perfectly. Thank you both for your help.

[MarcelBeug · Accepted solution]

It is a bit confusing if you want the values from Table 2 in the new column or if you want the name of the new column to be the concatenation of those values (as the topic title suggests).

Anyhow, the following code does both (it's Power Query code in an Excel workbook; Table 2 was already loaded in PQ with connection only). It's basic code without any checks and/or error handling:

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Text", type text}, {"Level", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", (x) => Table2[Item]{List.PositionOf(Table.Column(Table2, x[Text]),List.Min(List.Select(Table.Column(Table2, x[Text]), each _ > x[Level])))}),
    #"Renamed Columns" = Table.RenameColumns(#"Added Custom",{{"Custom", Text.Combine(#"Added Custom"[Custom])}})
in
    #"Renamed Columns"