Transpose dates column dynamically using specific column names

[NCPATZER]

I get an output daily from an SQL query with varying dates (not dependent on current date). I'd like to transpose it like the sample below, and have a dynamic naming, that the oldest date would be a column named "Day 1", then the second oldest date as "Day 2", etc. Appreciate any help anyone could provide.

[dufoq3 · Accepted solution]

Hello @NCPATZER,

You can choose output column names in 3rd step Variant (choose day or date😞

Result - Variant day (see column names)

Result - Variant date (see column names)

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcktNUjDVUTAyMDJR0lFyBGJDA6VYHYiEGaqEKVzCHFXCCC6BMMoJVcIMRcISi0kgcTMsBjkDsTkWc0DiRhZYDAJLAN0aCwA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, Item = _t, Amount = _t]),
    ChangedType = Table.TransformColumns(Source, {{"Date", each Date.FromText(_, [Format="MMM %d, yyyy", Culture="en-US"]), type date}, {"Amount", each Number.From(_), type number}}),
    // Use "day" or "date". This will infulence final result.
    Variant = "day",
    StepBack = ChangedType,
    #"Grouped Rows" = Table.Group(StepBack, {"Item"}, {{"All", each _, type table}}),
    Ad_FinalTable = Table.AddColumn(#"Grouped Rows", "Final Table", each
        List.Accumulate(
            {0..Table.RowCount([All])-1},
            #table(type table[Item=text], {{[Item]}}),
            (s,c)=> 
                if Variant = "day" then Table.AddColumn(s, "Day " & Text.From(c+1), (x)=> [All]{c}[Amount], type number)
                else Table.AddColumn(s, Date.ToText([All]{c}[Date], "MMM %d", "en-US"), (x)=> [All]{c}[Amount], type number)
        ), type table
    ),
    CombinedTables = Table.Combine(Ad_FinalTable[Final Table])
in
    CombinedTables

[NCPATZER]

Thanks! It worked.

[ronrsnfld]

In Power Query (Transform Data option), you can group by Item, Pivot and rename the columns.

The code below will work if you have only three distinct dates in each daily output table. If you might have more, some editing will be needed depending on your desired results in that instance:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMtI31DcyUdJRcgRiQwOlWB2QmBGSmClUzBhJzAgqBtXrhCxmhBCzRNUKEjJD1ekMxOaoGkFCRhaoOsFiQIfEAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, Item = _t, Amount = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{
        {"Date", type date}, {"Item", type text}, {"Amount", Int64.Type}}),
    
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Item"}, {
       {"Pivot", each 
            let 
                #"Date to Text" = Table.TransformColumnTypes(Table.Sort(_, {"Date", Order.Ascending}), {"Date", type text}),
                #"Pivot" = Table.Pivot(#"Date to Text", List.Distinct(#"Date to Text"[Date]),"Date","Amount", List.Sum),
                #"Rename" = Table.RenameColumns(#"Pivot", 
                    List.Zip({List.RemoveFirstN(Table.ColumnNames(#"Pivot"),1), {"Day 1","Day 2","Day 3"}}))
            in 
                #"Rename",
                type table[Item=text, Day 1=Int64.Type, Day 2 = Int64.Type, Day 3=Int64.Type]}}),
   
    #"Expanded Pivot" = Table.ExpandTableColumn(#"Grouped Rows", "Pivot", {"Day 1", "Day 2", "Day 3"})      
in
    #"Expanded Pivot"

[NCPATZER]

Thanks for this, but my data has varying number of dates (hundreds, actually). Sorry, if I didn't mention  that.

[lbendlin]

Let the data model and Power BI do that for you. Keep your source data in the format it is in, it is a good format.

Power BI does not support dynamic bucketing, you need to bring your own buckets.  In your case a static table with Day 1, Day 2 etc  in a single column reference table that you can then use for the columns in your visual.