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.
In my source data, a category of data is preceded by a title. For example: "MTD Waste" is followed by rows with each type of waste. Because those waste types can also be included in different categories (i.e. quality hold), I need to be able to distinguish the category. In excel I would do something like shown below: =+IF(D153="MTD Winder Culls","MTD_Waste",IF(D153="Total MTD Cull:","",K152)).
I could add the column to the worksheet with the data being retrieved, but this could cause problems. Is there a way to embed this type of logic within the query. The solution may somehow incorporate FillDown, but I haven't figured it out. I am at a very novice level.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8g1xUShXitWJVkoEk0lgMhlMpoDJVDAJUgcRTYQqhKqEKgWqjQUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [col1 = _t]),
#"Grouped Rows" = Table.Group(Source, {"col1"}, {"mtd_waste", each List.Transform([col1],(r)=>if Text.Contains([col1]{0},"w") then "MTD_Waste" else "")},GroupKind.Local,(x,y)=>Number.From(Text.Start(y[col1],3)="MTD"))
in
#"Grouped Rows"
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8g1xUShXitWJVkoEk0lgMhlMpoDJVDAJUgcRTYQqhKqEKgWqjQUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [col1 = _t]),
#"Grouped Rows" = Table.Group(Source, {"col1"}, {{"mtd_waste", each Table.AddColumn(_, "xxx",(r)=>if Text.Contains([col1]{0},"w") then "MTD_Waste" else "")}},GroupKind.Local,(x,y)=>Number.From(Text.Start(x[col1],3)=Text.Start(y[col1],3))),
#"Expanded mtd_waste" = Table.ExpandTableColumn(#"Grouped Rows", "mtd_waste", {"col1", "xxx"}, {"mtd_waste.col1", "mtd_waste.xxx"})
in
#"Expanded mtd_waste"
@Anonymous ,Can you share sample data and sample output in table format?
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.