CODEWITHSUNDEEP
excelexcel-formulapowerquery
sqlaficionado
sqlaficionado

Reputation: 33

Power M query syntax to get the value for a named cell in Excel

I am still learning about Power Query and Power M and I'm trying to get the value of a specific "named" cell in Excel and use this in Power M. It is just a single cell and

weekone

=Record.Field(Excel.CurrentWorkbook(){[Name="weekone"]}[Content]{0},Excel.CurrentWorkbook(){[Name="weekone"]}[Content]{0})

Maybe I am not understanding the syntax of how to reach information in a particular field correctly, or I am getting mixed up on how to use the Record.Field() function.

Any help or guidance that can be provided would be greatly appreciated! Thanks!

Upvotes: 2

Views: 5459

Answers (1)

MarcelBeug
MarcelBeug

Reputation: 2967

Record.Field gives the value of a field in a record. It takes the record as the first argument and the name of the field as the second argument.

In a step by step approach it will be clearer:

let
    Source = Excel.CurrentWorkbook(){[Name="weekone"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type date}}),
    FirstRecord = #"Changed Type"{0},
    RecordValue = Record.Field(FirstRecord,"Column1")
in
    RecordValue

Or, in 1 line:

= DateTime.Date(Record.Field(Excel.CurrentWorkbook(){[Name="weekone"]}[Content]{0},"Column1"))

This would be an alternative:

= DateTime.Date(Excel.CurrentWorkbook(){[Name="weekone"]}[Content]{0}[Column1])

My preference would be:

= DateTime.Date(Table.FirstValue(Excel.CurrentWorkbook(){[Name="weekone"]}[Content]))

Upvotes: 1

Related Questions