Forum Discussion
Helper Igroup the value wherever is break
Hi Team, I have a one column want to create new column problem is whenereve there is value other than one make it in a new group
how to achive the result in power query or dax calculation below is sample input column and output column
Input Column Output Column
1 G1
1 G1
1 G1
1 G1
23 G2
1 G2
1 G2
12 G3
1 G3
1 G3
1 G3
43 G4
1 G4
2 Replies
- AlienSx
Super User
let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content], g = Table.Group(Source, "Input Column", {{"all", each _}}, GroupKind.Local, (s, c) => Number.From(c <> 1)), idx = Table.AddIndexColumn(g, "Output Column", 1, 1), prefix = Table.TransformColumns(idx, {"Output Column", each "G" & Text.From(_)}), r = Table.RemoveColumns(prefix,{"Input Column"}), e = Table.ExpandTableColumn(r, "all", {"Input Column"}) in e - ToddChitt
Try this in Power Query:
Add a Custom Column with a formula like this:
if [input column] = 1 then null else [input column]
Now do a FILL DOWN operation on this column. Note that the first group G1 may be NULL so you may also have to do a REPLACE VALUES and replace NULL with some unique string or number.
Next do a GROUP BY of this column
