Need help in Power Query

[Baskar]

Thanks Advanced !!!

Value is the column i have in Power Query.

Index is expected output

How can i achieve in Power Query.  

@MattAllington@MarcelBeug@parry2k@ImkeF

[ImkeF]

This is an option which should be relatively easy to follow along:

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Added Index" = Table.AddIndexColumn(Source, "Index", 0, 1),
    #"Added Custom" = Table.AddColumn(#"Added Index", "Custom", each if [Value]=1 then [Index] else null),
    #"Filled Down" = Table.FillDown(#"Added Custom",{"Custom"}),
    #"Removed Columns" = Table.RemoveColumns(#"Filled Down",{"Index"}),
    #"Grouped Rows" = Table.Group(#"Removed Columns", {"Custom"}, {{"Index", each Table.AddIndexColumn(_,"Index",1,1), type table}}),
    #"Expanded Index" = Table.ExpandTableColumn(#"Grouped Rows", "Index", {"Value", "Index"}, {"Value", "Index.1"}),
    #"Removed Columns1" = Table.RemoveColumns(#"Expanded Index",{"Custom"})
in
    #"Removed Columns1"

Imke Feldmann

www.TheBIccountant.com -- How to integrate M-code into your solution  -- Check out more PBI- learning resources here

[Baskar]

@ImkeF I don't know how to expose my happiness. Thanks a lot.

Have similar case hope u will help me on this case too.

with the value now am expecting this result as output

[ImkeF · Accepted solution]

No prob, just a small modification:

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Added Index" = Table.AddIndexColumn(Source, "Index", 0, 1),
    #"Added Custom" = Table.AddColumn(#"Added Index", "Custom", each if [Value]=1 then [Index] else null),
    #"Filled Down" = Table.FillDown(#"Added Custom",{"Custom"}),
    #"Removed Columns" = Table.RemoveColumns(#"Filled Down",{"Index"}),
    #"Grouped Rows" = Table.Group(#"Removed Columns", {"Custom"}, {{"Index", each Table.AddIndexColumn(_,"Index",1,1), type table}}),
    YourNewValue = Table.AddIndexColumn(#"Grouped Rows", "Result", 1, 1),   
    #"Expanded Index" = Table.ExpandTableColumn(YourNewValue, "Index", {"Value", "Index"}, {"Value", "Index.1"}),
    #"Removed Columns1" = Table.RemoveColumns(#"Expanded Index",{"Custom"})
in
    #"Removed Columns1"

Imke Feldmann

www.TheBIccountant.com -- How to integrate M-code into your solution  -- Check out more PBI- learning resources here

[Zubair_Muhammad]

@Baskar

I know a DAX solution with little help from Power Query

First we add an Index Column as usual starting from 1 using POwer Query

Then we can use this Calculated Column to get the Desired New Index

New Index =
VAR lAst =
    CALCULATE (
        LASTNONBLANK ( TableName[Index], 1 ),
        FILTER (
            ALL ( TableName ),
            TableName[Index] < EARLIER ( TableName[Index] )
                && TableName[Value] = 1
        )
    )
RETURN
    IF (
        TableName[Value] = 1,
        1,
        CALCULATE (
            COUNTROWS ( TableName ),
            FILTER (
                ALL ( TableName ),
                TableName[Index] < EARLIER ( TableName[Index] )
                    && TableName[Index] >= lAst
            )
        )
            + 1
    )