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.
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
Solved! Go to 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
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
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
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.
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.
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
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 |
---|---|
114 | |
100 | |
83 | |
70 | |
61 |
User | Count |
---|---|
149 | |
114 | |
107 | |
89 | |
67 |