Forum Discussion
Identify a value in a column and repeat it until another specific value is found
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.
3 Replies
Anonymous ,Can you share sample data and sample output in table format?
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"
