Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
NCPATZER
Frequent Visitor

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.

 

sample.png

1 ACCEPTED SOLUTION
dufoq3
Super User
Super User

Hello @NCPATZER,

 

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

 

Result - Variant day (see column names)

dufoq3_0-1707480214413.png

 

Result - Variant date (see column names)

dufoq3_1-1707480255308.png

 

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

Note: Check this link to learn how to use my query.
Check this link if you don't know how to provide sample data.

View solution in original post

5 REPLIES 5
dufoq3
Super User
Super User

Hello @NCPATZER,

 

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

 

Result - Variant day (see column names)

dufoq3_0-1707480214413.png

 

Result - Variant date (see column names)

dufoq3_1-1707480255308.png

 

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

Note: Check this link to learn how to use my query.
Check this link if you don't know how to provide sample data.

Thanks! It worked.

ronrsnfld
Super User
Super User

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"

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

lbendlin
Super User
Super User

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.

Helpful resources

Announcements
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.

Top Solution Authors