asked on

retrieve value of cell in Power Query

How do I retrieve a particular group cells

value (date) displayed in a row in the datasource from Power Query.

I need to be able to retrieve these values (date) and set them as the column header. However when I attempt to use promote to column header, it fails and does not display the dates as column headers, but does display the text/value from the other columns without issue.

what am I missing? Need urgent assistance.

Thanks,

K

Tom Farrar

When you create the table in Power Query there is an option that says the first row is column headers. Are you checking that?

Karen Schaefer

ASKER

Yes that doesn't seem to work when the data is a date, the other non-date fields promote to header.

Here is my latest code attempting to use a function found on web. Note sure how to implemented correctly: Actually would prefer to look at the value of existing sheet then have the user input the parameter each time.

let GetValue=(DateUsed) => 
    let
      name = Excel.CurrentWorkbook(){[Name=DateUsed]}[Content],
      value = name{0}[Column3]
    in
      value
in
    GetValue

Open in new window

let

    #"Day1 Label" = Date.ToText ( Date.AddDays ( GetValue, 1 ) ),
    #"Day2 Label" = Date.ToText ( Date.AddDays ( GetValue, 2 ) ),
    #"Day3 Label" = Date.ToText ( Date.AddDays ( GetValue, 3 ) ),
    #"Day4 Label" = Date.ToText ( Date.AddDays ( GetValue, 4 ) ),
    #"Day5 Label" = Date.ToText ( Date.AddDays ( GetValue, 5 ) ),
    #"Day6 Label" = Date.ToText ( Date.AddDays ( GetValue, 6 ) ),
    #"Day7 Label" = Date.ToText ( Date.AddDays ( GetValue, 7 ) ),
    #"Day9 Label" = Date.ToText ( Date.AddDays ( GetValue, 8 ) ),
    
    Source = Excel.Workbook(File.Contents("\\ant\dept\sandop\BIGGulpReport\INBOUND_FORECAST_SUMMARYReport.xlsx"), null, true),
    US_BIG_GULP_STRING_Sheet = Source{[Item="US_BIG_GULP_STRING",Kind="Sheet"]}[Data],
    #"Removed Other Columns" = Table.SelectColumns(US_BIG_GULP_STRING_Sheet,{"Column1", "Column2", "Column3", "Column4", "Column5", "Column6", "Column7", "Column8", "Column9", "Column10"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Removed Other Columns",{{"Column3", Int64.Type}, {"Column4", Int64.Type}, {"Column5", Int64.Type}, {"Column6", Int64.Type}, {"Column7", Int64.Type}, {"Column8", Int64.Type}, {"Column9", Int64.Type}, {"Column10", Int64.Type}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type", each ([Column1] <> "Amazon Network" and [Column1] <> "Non Sortable" and [Column1] <> "Pantry" and [Column1] <> "Small Sortable" and [Column1] <> "Sortable" and [Column1] <> "Specialty") and ([Column2] = "New Vendor Freight ?")),
    #"Renamed Columns" = Table.RenameColumns(#"Filtered Rows",{{"Column1", "FC"}, {"Column2", "FC Filter"}, {"Column3", GetValue}}),
    #"Renamed Columns1" = Table.RenameColumns(#"Renamed Columns",{{"Column4", #"Day1 Label"}}),
    #"Renamed Columns2" = Table.RenameColumns(#"Renamed Columns1",{{"Column5", #"Day2 Label"}}),
    #"Renamed Columns3" = Table.RenameColumns(#"Renamed Columns2",{{"Column6", #"Day3 Label"}}),
    #"Renamed Columns4" = Table.RenameColumns(#"Renamed Columns3",{{"Column7", #"Day4 Label"}}),
    #"Renamed Columns5" = Table.RenameColumns(#"Renamed Columns4",{{"Column8", #"Day5 Label"}}),
    #"Renamed Columns6" = Table.RenameColumns(#"Renamed Columns5",{{"Column9", #"Day6 Label"}}),
    #"Renamed Columns7" = Table.RenameColumns(#"Renamed Columns6",{{"Column10", #"Day7 Label"}}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Renamed Columns7", {"FC", "FC Filter"}, "Attribute", "Value")
in
    #"Unpivoted Columns"

Open in new window

= Excel.Workbook(File.Contents(GetValue("SourceFile")))

Open in new window

Tom Farrar

Okay. I put dates in the first row of a spreadsheet with data, and checked first row contains header, and I didn't have a problem getting the dates as header. Are you using Power Query > From Table?

Karen Schaefer

ASKER

no, from data source see my last post.

Tom Farrar

Sorry I am not that familiar with Power Query coding behind the scenes. Is "data source" an option in the Power Query menu?

ASKER CERTIFIED SOLUTION

Karen Schaefer

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Martin Liss

This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.