Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago

Power query assistance

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

General Comment

1 Reply

  • serpiva64's avatar
    serpiva64
    Solution Sage
    3 years ago

    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!