Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe 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.
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.
Solved! Go to 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
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
Thanks! It worked.
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.
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.
User | Count |
---|---|
23 | |
17 | |
14 | |
12 | |
11 |