0

how do I get the queries for the following scenarios.

Scenario 1: I have a column MAX which contains the max value out of defined columns.

Q1: How do I get the column name for this value?

(A solution I had in the past was based on dynamic column names, which did not work.)

Scenario 2: I have a table which I want to split by columns.

Q2: Table 1: [X],[Z],[A],[Y],[B] Table 2: [Z],[A],[Y]

Source:

Key X   A   Z   Y   B
Cat 15  5   10  5   10
Cat 25  10      15  20
Cat 5   15  5   20  25
Dog 5   25  10  5   5
Dog 5       25      15
Bird    25  15  5   5   5

Here is a visualisation of my questions.

enter image description here

Many Thanks, Aykut

aynber
  • 22,380
  • 8
  • 50
  • 63

1 Answers1

0

If you want to find the max of hard coded columns A/Z/Y then add an index and use code

#"Add Max"=Table.AddColumn(#"Added Index" ,"Max",each List.Max( Record.ToList( Table.SelectColumns(#"Added Index" ,{"A","Z","Y"}){[Index]}) )),

If you already have a maximum column named Max and you want to find what column it comes from within hard-coded column names X/A/Z/Y/B then use code

#"Add Label"=Table.AddColumn(#"PriorStepName","MaxLabel",each {"X","A","Z","Y","B"}{List.PositionOf(Record.ToList( Table.SelectColumns(#"PriorStepName",{"X","A","Z","Y","B"}){[Index]}),[Max])})

doing both:

let Source = Excel.CurrentWorkbook(){[Name="Table14"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Key", type text}, {"X", Int64.Type}, {"A", Int64.Type}, {"Z", Int64.Type}, {"Y", Int64.Type}, {"B", Int64.Type}}),
#"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1),
#"Add Max"=Table.AddColumn(#"Added Index" ,"Max",each List.Max( Record.ToList( Table.SelectColumns(#"Added Index" ,{"A","Z","Y"}){[Index]}) )),
#"Add Label"=Table.AddColumn(#"Add Max","MaxLabel",each {"X","A","Z","Y","B"}{List.PositionOf(Record.ToList( Table.SelectColumns(#"Add Max",{"X","A","Z","Y","B"}){[Index]}),[Max])})
in #"Add Label"

If you want to select columns col1,col2,col3, with those names hardcoded, then just use

 #"Removed Other Columns" = Table.SelectColumns(#"priorstepname",{"col1", "col2", "col2"})

so specifically

Table1=Table.SelectColumns(#"priorstepname",{"X", "A", "Z","Y","B"}), Table2=Table.SelectColumns(#"priorstepname",{"A", "Z","Y"}),

horseyride
  • 17,007
  • 2
  • 11
  • 22
  • Hi horseyride, your proposal works as usual on my given example here. When I apply the adapted code on my real case 30Ts rows, 60 columns, whereas MAX() calculates out of 5 columns, it is extremely slow. So even in query editor I am done and can see all data, now when I want to load data back to excel it is very slow just to extract 3600 rows with 60 columns. Is there a way of improving the code? I read something about Table.buffer. Would this work? Do I need to apply only 1x when I open the excel file or do I need to use Table.buffer(table) on every line with output is a table? :-( –  Jan 10 '22 at 17:46
  • do you really need the name of the column with the maximum in it? Otherwise it should be much simpler and probably faster to do #"Added Custom" = Table.AddColumn(Source, "Custom", each List.Max({[X],[A],[Z],[Y],[B]})) Not sure if a Table.Buffer around #"Added Index" and #"Add Max" would help – horseyride Jan 10 '22 at 18:37
  • You are right, the code line where the column name of MAX is calculated is the performance killer ! I think Power Query need a inbuilt function like XLOOKUP. This might solve the performance issue :-) –  Jan 10 '22 at 19:48