Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 )
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
98 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
100 | |
86 | |
64 |