Repeat of the first 21 Values for every Product Group

[KJung]

Hey guys,

I'm having some problems with filtering some values of my data set.

Here's an example of my data: 

What I need is a way to repeat the first 21 sensor values for every product group to column A as shown in the picture.

I've tried several combinations but ended up getting the same values from column B insted of the first 21 repeatedly.

Thanks for any kind of help!

Best regards

[v-yulgu-msft · Accepted solution]

Hi @KJung,

First, please add a nested Index column in Query Editor.

let
 Source = Excel.Workbook(File.Contents("C:\Users\xxxx\Desktop\sampledata.xlsx"), null, true),
 Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
 #"Promoted Headers" = Table.PromoteHeaders(Sheet1_Sheet, [PromoteAllScalars=true]),
 #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Value", type number}, {"Sensor", Int64.Type}, {"Product", Int64.Type}}),
 #"Grouped Rows" = Table.Group(#"Changed Type", {"Product"}, {{"Partition", each _, type table}}),
 #"Added Index" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.AddIndexColumn([Partition], "Index", 1,1)),
 #"Removed Columns" = Table.RemoveColumns(#"Added Index",{"Partition"}),
 #"Expanded Custom" = Table.ExpandTableColumn(#"Removed Columns", "Custom", {"Value", "Sensor", "Index"}, {"Custom.Value", "Custom.Sensor", "Custom.Index"}),
 #"Renamed Columns" = Table.RenameColumns(#"Expanded Custom",{{"Custom.Value", "Value"}, {"Custom.Sensor", "Sensor"}, {"Custom.Index", "Index"}})
in
 #"Renamed Columns"

Then, in data view, create a calculated column with this DAX:

First =
LOOKUPVALUE (
    Sheet1[Value],
    Sheet1[Product], Sheet1[Product],
    Sheet1[Index], Sheet1[Sensor]
)

I have uploaded the .pbix file for your reference.

Best regards,

Yuliana Gu

[Greg_Deckler]

Can you post some sample/example data that we can copy and paste? Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

[KJung]

Thanks for your feedback.

I can't post the original data due to data security.  But I can link the excel file which is equivalent. I've also marked some areas to highlight the groups.

https://ufile.io/94wxh

The dark green values have to be used for the column 'first' until a new product number appears. The numbering of the sensors will never change.

[KJung]

Hey guys,

I'm having some problems with filtering some values of my data set.

Here's an example of my data: 

What I need is a way to repeat the first 21 sensor values of a product group in column A as shown in the picture.

I've tried several versions of FIRSTNONBLANK() but I end up getting the same values from column B but not the first 21 repeatedly.

Thanks for any kind of help!

Best regards