Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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
Solved! Go to 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
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
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
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
@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
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
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
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 )