Reply
Super User
Posts: 754
Registered: ‎06-03-2016
Accepted Solution

Need help in Power Query

Thanks Advanced !!!

Value is the column i have in Power Query.

Index is expected output

Capture.PNG


How can i achieve in Power Query.  

 

@MattAllington@MarcelBeug@parry2k@ImkeF


Accepted Solutions
Super User
Posts: 1,620
Registered: ‎09-06-2015

Re: Need help in Power Query

[ Edited ]

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

 

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Imke Feldmann

How to integrate M-code into your solution -- Check out more PBI- learning resources here -- Performance Tipps for M-queries




View solution in original post


All Replies
Super User
Posts: 1,620
Registered: ‎09-06-2015

Re: Need help in Power Query

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

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Imke Feldmann

How to integrate M-code into your solution -- Check out more PBI- learning resources here -- Performance Tipps for M-queries




Super User
Posts: 2,846
Registered: ‎09-27-2017

Re: Need help in Power Query

@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
    )

 

1045.png

Super User
Posts: 754
Registered: ‎06-03-2016

Re: Need help in Power Query

@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

Capture.PNG

Super User
Posts: 1,620
Registered: ‎09-06-2015

Re: Need help in Power Query

[ Edited ]

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

 

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Imke Feldmann

How to integrate M-code into your solution -- Check out more PBI- learning resources here -- Performance Tipps for M-queries