Forum Discussion

NCPATZER's avatar
NCPATZER
Frequent Visitor
2 years ago
Solved

Transpose dates column dynamically using specific column names

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 ...
Need Help
  • dufoq3's avatar
    dufoq3
    2 years ago

    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
ronrsnfld's avatar
ronrsnfld
Super User
2 years ago

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's avatar
NCPATZER
Frequent Visitor
2 years ago

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