Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
New to PBI, I've got a list of years in some data (think a list of movies and the years they were produced). I'm trying to find a way to display the missing years.
I've got them displayed using a bar chart, but I'm trying to find a way to do a count of years even though they aren't there. if that makes sense.
Think like years
1926
1930
1931
1933
1934
1935
1936
So I want to display the missing years 1927, 1928, 1929, 1932 as blanks. If this is possible.
I'm used to using ACL (Audit Command Language) and using a function called gaps) that just presents the gaps in the data. Whereas I don't see a function like that here. Or in Excel using v-lookup to find the missing years. So confused on how to do this here.
Solved! Go to Solution.
Little hard to know exactly what you want, but the following code will generate a List of all the years, with blanks for the missing years.
Algorithm should be obvious from the code. If not, ask.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMrQ0MlOK1QExjA1gDEMYwxjGMIExTGEMoK5YAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Years = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Years", Int64.Type}}),
#"Count of Years" = List.Max(#"Changed Type"[Years]) - List.Min(#"Changed Type"[Years])+1,
#"Display Missing Years as Blanks" =
let
#"All Years" = List.Numbers(List.Min(#"Changed Type"[Years]), #"Count of Years"),
#"Missing Years" = List.RemoveMatchingItems(#"All Years", #"Changed Type"[Years]),
#"Blanks" = List.ReplaceMatchingItems(#"All Years",List.Zip({#"Missing Years", List.Repeat({null}, List.Count(#"Missing Years"))}))
in
#"Blanks"
in
#"Display Missing Years as Blanks"
Original
Results
Thank you @ronrsnfld , This worked perfectly to what I was wanting to see happen.
Little hard to know exactly what you want, but the following code will generate a List of all the years, with blanks for the missing years.
Algorithm should be obvious from the code. If not, ask.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMrQ0MlOK1QExjA1gDEMYwxjGMIExTGEMoK5YAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Years = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Years", Int64.Type}}),
#"Count of Years" = List.Max(#"Changed Type"[Years]) - List.Min(#"Changed Type"[Years])+1,
#"Display Missing Years as Blanks" =
let
#"All Years" = List.Numbers(List.Min(#"Changed Type"[Years]), #"Count of Years"),
#"Missing Years" = List.RemoveMatchingItems(#"All Years", #"Changed Type"[Years]),
#"Blanks" = List.ReplaceMatchingItems(#"All Years",List.Zip({#"Missing Years", List.Repeat({null}, List.Count(#"Missing Years"))}))
in
#"Blanks"
in
#"Display Missing Years as Blanks"
Original
Results
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Ask questions in Eventhouse and KQL, Eventstream, and Reflex.
User | Count |
---|---|
39 | |
24 | |
21 | |
21 | |
13 |