Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi,
My fill down feature is not working as I want it to
Feature ID | User Story | Commence Date | What Power BI Fill Down will do | What I Want |
FID001 | 200 | 2/9/2021 | 2/9/2021 | 2/9/2021 |
FID001 | 202 | 2/9/2021 | 2/9/2021 | |
FID001 | 203 | 2/9/2021 | 2/9/2021 | |
FID002 | 200 | 2/10/2021 | 2/10/2021 | 2/10/2021 |
FID002 | 9999 | 2/10/2021 | 2/10/2021 | |
FID002 | 9999 | 2/10/2021 | 2/10/2021 | |
FID002 | 9999 | 2/10/2021 | 2/10/2021 | |
FID003 | 203 | 2/10/2021 | - | |
FID003 | 204 | 2/10/2021 | - | |
FID003 | 205 | 2/10/2021 | - | |
FID003 | 206 | 2/10/2021 | - |
I have the expected column and what power bi is doing right now.
I just want the fill down transformation of Power BI to reset at each Feature ID
Solved! Go to Solution.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcvN0MTAwVNJRMjIwAJH6lvpGBkaGSrE6KHJGQFIBXdAYVdAIyRRDAzRjQJKWQICphVhRY2x2QgRNsAmaYhM0gwjGAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Feature ID" = _t, #"User Story" = _t, #"Commence Date" = _t]),
#"Grouped Rows" = Table.Group(Source, {"Feature ID"}, {{"Data", each _, type table [Feature ID=nullable text, User Story=nullable text, Commence Date=nullable text]}}),
#"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each [Data]{0}[#"Commence Date"]),
#"Expanded Data" = Table.ExpandTableColumn(#"Added Custom", "Data", {"User Story", "Commence Date"}, {"User Story", "Commence Date"})
in
#"Expanded Data"
After collapsing the subsections they are all tables called "Data". From each table we grab the field "Commence Date" in the first row ( {0} ). We then use that information to emulate the "fill-down by subsection" behavior that you wanted to achieve
{ x } = get row x
[ y ] = get column y
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcvN0MTAwVNJRMjIwAJH6lvpGBkaGSrE6KHJGQFIBXdAYVdAIyRRDAzRjQJKWQICphVhRY2x2QgRNsAmaYhM0gwjGAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Feature ID" = _t, #"User Story" = _t, #"Commence Date" = _t]),
#"Grouped Rows" = Table.Group(Source, {"Feature ID"}, {{"Data", each _, type table [Feature ID=nullable text, User Story=nullable text, Commence Date=nullable text]}}),
#"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each [Data]{0}[#"Commence Date"]),
#"Expanded Data" = Table.ExpandTableColumn(#"Added Custom", "Data", {"User Story", "Commence Date"}, {"User Story", "Commence Date"})
in
#"Expanded Data"
Thanks
Can you also explain this step:
[Data]{0}[#"Commence Date"]
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
99 | |
73 | |
72 | |
49 |
User | Count |
---|---|
145 | |
109 | |
109 | |
90 | |
64 |