How to get the alphabetical coordinates of columns in excel

[smpa01]

Hi,

I am currently working on converting an excel table with >100 calculated columns into a DAX table with similar calculated columns.

Excel formula refers to the columns by their column coordinates, e.g. sum = A2+B2

whereas DAX refers the column name in the formula, e.g. sum = Principal +Interest

Now, when I am trying to convert excel version of sum=A2+B2, is there any clever way for me to find out A=Principal and B= Interest in the spreadsheet.

I am talking about a table which has >50 non calculated columns and >100 calculated columns to be converted to comparable DAX model. Every time I look into a formula for the calculated columns in excel,
e.g.

Interest = =IF(AND(AR7="Pending",OR(V7="Completed",V7="Approved", LEFT(V7,4)="OPEN")),IF(U7*1>0,U7*1,N7)/VLOOKUP(F7,Tax!A:B,2,0),0)

I need to go back and forth in the table to find out what are the corresponding column for each coordinate in this.

Is there any way for me to find out what column names correspond to which column coordinate

e.g., how do I know here that A=name, B=Address, C=Debt

Thank you in advance.

[smpa01 · Accepted solution]

I think I manage to solve it using Power query. The following generates a table for an excel table with columns from A-ZZ and their corresponding coordinates.

Table Name - Query1

let
Source = {"A","B","C","D","E","F","G","H","I","J","K","L","M","N","O","P","Q","R","S","T","U","V","W","X","Y","Z"},
#"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Added Custom" = Table.AddColumn(#"Converted to Table", "Custom", each {"A","B","C","D","E","F","G","H","I","J","K","L","M","N","O","P","Q","R","S","T","U","V","W","X","Y","Z"}),
#"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Custom"),
#"Inserted Merged Column" = Table.AddColumn(#"Expanded Custom", "Merged", each Text.Combine({[Column1], [Custom]}, ""), type text),
#"Removed Columns" = Table.RemoveColumns(#"Inserted Merged Column",{"Column1", "Custom"}),
#"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Merged", "Column1"}}),
Custom1 = #"Converted to Table"&#"Renamed Columns",
#"Added Index" = Table.AddIndexColumn(Custom1, "Index", 1, 1) in
#"Added Index"

Now if I table like following, I run COLUMN syntax on my data source columns it produces the column number and I can use the previous table to this to get the names

Then

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WUtLBQLE60UoupcnZCkCOe35+cSqQds4vB3EDMtNT8/OADI/8ouJUqAiIcs7ITM5OzQPrNQTyjYDYGIhNgNgUiM2A2FwpNhYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t, Column4 = _t, Column5 = _t, Column6 = _t, Column7 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}, {"Column5", type text}, {"Column6", type text}, {"Column7", type text}}),
    #"Removed Top Rows" = Table.Skip(#"Changed Type",1),
    #"Transposed Table" = Table.Transpose(#"Removed Top Rows"),
    #"Changed Type1" = Table.TransformColumnTypes(#"Transposed Table",{{"Column2", Int64.Type}}),
    #"Merged Queries" = Table.NestedJoin(#"Changed Type1", {"Column2"}, Query1, {"Index"}, "Query1", JoinKind.LeftOuter),
    #"Expanded Query1" = Table.ExpandTableColumn(#"Merged Queries", "Query1", {"Column1"}, {"Column1.1"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded Query1",{"Column2"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Column1.1", "Alphabetical_Coordinate"}})
in
    #"Renamed Columns"

which gives me this