retrieve value of cell in Power Query

How do I retrieve a particular group cellsscreenshot 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
Karen SchaeferBI ANALYSTAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

tomfarrarCommented:
When you create the table in Power Query there is an option that says the first row is column headers.  Are you checking that?
0
Karen SchaeferBI ANALYSTAuthor Commented:
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

0
tomfarrarCommented:
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?
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Karen SchaeferBI ANALYSTAuthor Commented:
no, from data source see my last post.
0
tomfarrarCommented:
Sorry I am not that familiar with Power Query coding behind the scenes.  Is "data source" an option in the Power Query menu?
0
Karen SchaeferBI ANALYSTAuthor Commented:
Please close found solution elswhere.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Martin LissOlder than dirtCommented:
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.