Power query assistance

[Anonymous]

Power query

I would like to flatten or unpivot the dates columns, but because these column header change in future my query will break is there a way to unpivot these columns without the query breaking in future when column headers change? The date columns are daily customer requirements 

Dataset

product    available stock   07.02.2023  08.02.2023

b365                20                     0                15

b379                 14                    5                 7

the main aim is to return the date when stock runs out

[serpiva64]

Hi,

You can get this

I think i've found a really short way and it seems all dynamic

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSjI2M1XSUTIyABIgbGiqFKsDEja3BPFMgARI3lwpNhYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [product = _t, available_stock = _t, #"07.02.2023" = _t, #"08.02.2023" = _t]),
#"Grouped Rows" = Table.Group(Source, {"product", "available_stock"}, {{"Count", each _, type table [product=nullable text, available_stock=nullable text, 07.02.2023=nullable text, 08.02.2023=nullable text]}}),
#"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.Transpose( Table.DemoteHeaders( Table.RemoveColumns([Count],{"product", "available_stock"})))),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Count"}),
#"Expanded Custom" = Table.ExpandTableColumn(#"Removed Columns", "Custom", {"Column1", "Column2"}, {"Column1", "Column2"}),
#"Renamed Columns" = Table.RenameColumns(#"Expanded Custom",{{"Column1", "Date"}, {"Column2", "Value"}})
in
#"Renamed Columns"

You can take a look at the attached file

If this post is useful to help you to solve your issue, consider giving the post a thumbs up and accepting it as a solution!