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 column named "Day 1", then the second oldest date as "Day 2", etc. Appreciate any help anyone could provide.

 

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

5 Replies

Replies have been turned off for this discussion
  • 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.

  • lbendlin's avatar
    lbendlin
    Super User
    2 years ago

    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.

  • dufoq3's avatar
    dufoq3
    Community Champion
    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