Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
tgjones43
Helper IV
Helper IV

Custom column based on several other columns

This is a tricky one to describe, so here goes. I have attached a much simplified example. The data relates to fish sampling data in rivers.

 

I have a table with thousands of 8-digit numbers in the first column ([ID] - only one number is given in the example). Each 8-digit number occupies 30-40 rows, with data in several other columns. In one of those other columns ([ID 2]), and for some rows only, is another 8-digit number that is linked to values in other columns and is repeated several times. The value in [ID 2] is assigned when a new survey is undertaken, but the names in columns 3 and 4 can be the same, if the survey type is the same as the previous survey type (e.g. rows 4 and 5).

 

What I need to do is have some way of creating a custom column that assigns a value of Survey 1, Survey 2, Survey 3 to match the values in [ID 2] i.e. all values of 21779013 = Survey 1.

 

That would be easy if there weren't thousands of values in [ID], with each value of [ID] having unique values in [ID 2] i.e. for the next value of [ID] (21770162), the values in [ID 2] would be totally different, and unique to that particular value in [ID].

 

Does anyone have any ideas? Thank you!

 

Fish example.png

9 REPLIES 9
Greg_Deckler
Super User
Super User

Given the data shown, what is the expected output? Also, can you post that data as text instead of a picture? 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


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

Thank you for the response. I have pasted the data below, with the column at the end showing the expected outcome.

 

IDID 2NameValueCustom Column
21770161nullAltitude284null
21770161nullWidth5null
2177016121779013ProgramCore Fisheries Monitoring ProgramSurvey 1
2177016121779155ProgramCore Fisheries Monitoring ProgramSurvey 2
2177016121779296ProgramWater Framework Directive ProgrammeSurvey 3
2177016121779013PurposeTemporalSurvey 1
2177016121779155PurposeTemporalSurvey 2
2177016121779296PurposeSalmonidSurvey 3
2177016121779013Repeat frequency1 yearSurvey 1
2177016121779155Repeat frequency1 yearSurvey 2
2177016121779296Repeat frequency6 yearsSurvey 3
2177016121779013Survey StrategyQuanitativeSurvey 1
2177016121779155Survey StrategySemi-quantitativeSurvey 2
2177016121779296Survey StrategySemi-quantitativeSurvey 3
2177016121779013Guideline MethodPDC Electric FishingSurvey 1
2177016121779155Guideline MethodPDC Electric FishingSurvey 2
2177016121779296Guideline MethodElectric FishingSurvey 3

...a minor tweek to Lin's excellent solution, to match the 'expected outcome' above.

Kind regards, Ola

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("rZNRT8IwFIX/SrNnTFh1Qx4NiE8k6kx4IDw07AqN3TruWsz+vXdMjNal2WBPvevuOflu27NeBzycTMZhHAajILdK0fKgjDQ2BSr5/V2wGbU1rWRq9rRG7v+6nI7DWyqfUe9QZFTNNAJbyHIPKKFkS51Lo1HmO3buaXMJo2gAFz6N/7ishAFkC/qAT40fbC4RtkYe4eySgW8mi4Uu67N5g6zQKJQPvVvzN+FPcyJURrOlHoxXKEAY9o5wsJBvK9oKWQUCPTT9NA1UiyY+aUoPXGLxCBVLDNJR72rNixV0V6I+ZQ/gf10Cmbw5kNj41A3qpeqG+cnKFJTMgS3B7HVaX8h8xh4VvQ2U29Ozo5fmob/GoZmgxcGn5l0i+6upNbJ8kMjyYIjI8mCYyLozeVPoondr7hJZB6NT/Byafpo+kXXgOkfWAewZOgf1UvXFkXXor3HoEdnNFw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [ID = _t, #"ID 2" = _t, Name = _t, Value = _t]),
    #"Grouped and Add.SortNo" = Table.Group(Source, {"ID"}, {{"Count", each Table.AddIndexColumn(_, "Sort",1,1), type table}}),
    #"Expanded Count1" = Table.ExpandTableColumn(#"Grouped and Add.SortNo", "Count", {"ID 2", "Name", "Value", "Sort"}, {"ID 2", "Name", "Value", "Sort"}),
    Group = Table.Group(#"Expanded Count1", {"ID 2"}, {{"Count", each _, type table}}),
    #"Added Add.SurveyNo (index)" = Table.AddIndexColumn(Group, "Custom Column", 0, 1),
    #"Changed Type" = Table.TransformColumnTypes(#"Added Add.SurveyNo (index)",{{"Custom Column", type text}}),
    #"Added Prefix" = Table.TransformColumns(#"Changed Type", {{"Custom Column", each "Survey " & _, type text}}),
    #"Removed Columns" = Table.RemoveColumns(#"Added Prefix",{"ID 2"}),
    #"Replaced Value" = Table.ReplaceValue(#"Removed Columns","Survey 0",null,Replacer.ReplaceValue,{"Custom Column"}),
    #"Expanded Count" = Table.ExpandTableColumn(#"Replaced Value", "Count", {"ID", "ID 2", "Name", "Sort", "Value"}, {"ID", "ID 2", "Name", "Sort", "Value"}),
    #"Sorted Rows" = Table.Sort(#"Expanded Count",{{"ID", Order.Ascending}, {"Sort", Order.Ascending}}),
    #"Removed SortNo" = Table.RemoveColumns(#"Sorted Rows",{"Sort"})
in
    #"Removed SortNo"

hi, @Ola_S

Could you please tell me if your problem has been solved? If it is, could you please mark the helpful replies as Answered?

 

Best Regards,
Lin

 

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hi @v-lili6-msft and @Ola_S. Thank you so much for your help. I have only just got round to trying the code. It works very well, but it doesn't quite match the expected outcome...

 

I have pasted a slightly longer view of my data below, this time with two values for [ID]. The main problem is that where [ID2] is null, the value in [Expected Outcome] should also be null, but as you can see in [Custom Column], it is displaying Survey 1. And it doing this for every null value in [ID2], no matter what the value of [ID].

 

Can you suggest a tweek to your solution?

 

Thank you.

 

 

IDID 2NameValueCustom ColumnExpected Outcome
21771061nullAltitude - 84Survey 1null
21771061nullArea - South EastSurvey 1null
21771061nullCEH 50K catchment id - 196541 - Severn Upper above Montfort C054001ISurvey 1null
21771061nullCEH 50k catchment id number196541Survey 1null
2177106121778928Site purpose - First survey01/01/2015Survey 3Survey 2
2177106121778928Site purpose - Guideline methodElectric FishingSurvey 3Survey 2
2177106121776925Site purpose - Guideline methodElectric FishingnullSurvey 1
2177106121777935Site purpose - Guideline methodElectric FishingSurvey 2Survey 3
2177106121777935Site purpose - ProgramCore Fisheries Monitoring ProgrammeSurvey 2Survey 3
2177106121776925Site purpose - ProgramCore Fisheries Monitoring ProgrammenullSurvey 1
2177106121778928Site purpose - ProgramCore Fisheries Monitoring ProgrammeSurvey 3Survey 2
2177106121777935Site purpose - PurposeSAP / ST - SpatialSurvey 2Survey 3
2177106121778928Site purpose - PurposeSAP / ST - SpatialSurvey 3Survey 2
2177106121776925Site purpose - PurposeCoarse - TemporalnullSurvey 1
2177106121777935Site purpose - Repeat frequency6 YearsSurvey 2Survey 3
2177106121776925Site purpose - Repeat frequency1 YearnullSurvey 1
2177106121778928Site purpose - Repeat frequency6 YearsSurvey 3Survey 2
2177106121776925Site purpose - Repeat from01/01/2001nullSurvey 1
2177106121778928Site purpose - Repeat from01/01/2012Survey 3Survey 2
2177106121777935Site purpose - Repeat from01/01/2001Survey 2Survey 3
2177106121776925Site purpose - Repeat until31/12/2006nullSurvey 1
2177106121778928Site purpose - Repeat until31/12/2025Survey 3Survey 2
2177106121777935Site purpose - Repeat until31/12/2006Survey 2Survey 3
2177106121776925Site purpose - Survey after01/01/2001nullSurvey 1
2177106121778928Site purpose - Survey after01/01/2012Survey 3Survey 2
2177106121777935Site purpose - Survey after01/01/2001Survey 2Survey 3
2177106121777935Site purpose - Survey before31/12/2001Survey 2Survey 3
2177106121776925Site purpose - Survey before31/12/2001nullSurvey 1
2177106121778928Site purpose - Survey before31/12/2012Survey 3Survey 2
2177106121777935Site purpose - Survey strategyQualitativeSurvey 2Survey 3
2177106121778928Site purpose - Survey strategyCPUE/TSurvey 3Survey 2
2177106121776925Site purpose - Survey strategyQuantitativenullSurvey 1
2177106121776925Site purpose - Target guildCoarsenullSurvey 1
2177106121778928Site purpose - Target guildSalmon / Sea TroutSurvey 3Survey 2
2177106121777935Site purpose - Target guildSalmon / Sea TroutSurvey 2Survey 3
21771062nullAltitude - 82Survey 1null
21771062nullArea - South EastSurvey 1null
21771062nullCEH 50K catchment id - 196541 - Severn Upper above Montfort C054001ISurvey 1null
21771062nullCEH 50k catchment id number196541Survey 1null
2177106221777936Site purpose - First survey01/01/2015Survey 5Survey 5
2177106221777936Site purpose - Guideline methodElectric FishingSurvey 5Survey 5
2177106221776926Site purpose - Guideline methodElectric FishingSurvey 4Survey 4
2177106221777936Site purpose - ProgramCore Fisheries Monitoring ProgrammeSurvey 5Survey 5
2177106221776926Site purpose - ProgramCore Fisheries Monitoring ProgrammeSurvey 4Survey 4
2177106221776926Site purpose - PurposeCoarse - TemporalSurvey 4Survey 4
2177106221777936Site purpose - PurposeSAP / ST - SpatialSurvey 5Survey 5
2177106221776926Site purpose - Repeat frequency1 YearSurvey 4Survey 4
2177106221777936Site purpose - Repeat frequency6 YearsSurvey 5Survey 5
2177106221777936Site purpose - Repeat from01/01/2001Survey 5Survey 5
2177106221776926Site purpose - Repeat from01/01/2001Survey 4Survey 4
2177106221777936Site purpose - Repeat until31/12/2025Survey 5Survey 5
2177106221776926Site purpose - Repeat until31/12/2006Survey 4Survey 4
2177106221776926Site purpose - Survey after01/01/2001Survey 4Survey 4
2177106221777936Site purpose - Survey after01/08/2013Survey 5Survey 5
2177106221777936Site purpose - Survey before31/10/2013Survey 5Survey 5
2177106221776926Site purpose - Survey before31/12/2001Survey 4Survey 4
2177106221776926Site purpose - Survey strategyQuantitativeSurvey 4Survey 4
2177106221777936Site purpose - Survey strategyCPUE/TSurvey 5Survey 5
2177106221777936Site purpose - Target guildSalmon / Sea TroutSurvey 5Survey 5
2177106221776926Site purpose - Target guildCoarseSurvey 4Survey 4

hi, @tgjones43

What is your expected outcome Survey 1, Survey 2, Survey 3, Is it an index column for each ID and ID2?

You may try this way as below:

Step1:

Duplicate the basic query, then group by it as below:

5.JPG6.JPG

Step2:

Filter null value for ID2 and remove Count column

7.JPG

Step3:

Add an index column by ID

= Table.Group( #"Filtered Rows", {"ID"}, {{"Partition", each Table.AddIndexColumn(_, "Index",1,1), type table}})

and expand the column

8.JPG

 

 

 

Step4:

Merge the tables and expand the column

9.JPG

Step5:

Change the index column to text and add a custom column as below

=if [#"Table1 (2).Index"] = null then null else "Survey" &  [#"Table1 (2).Index"]

Result:

10.JPG

Here is pbix file, please try it.

 

Best Regards,

Lin

 

 

 

 

 

 

 

 

 

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hi @v-lili6-msft

 

Thank you, that looks great. But is there a way to do this without creating a second query? I would like to include the functions you have described after several steps and would prefer everything to be in one query. 

HI, @tgjones43

Try this pbix file, Table1 (3) in Edit Queries.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("nZJBb8IwDIX/StQzk0hZyzhOMHZCYuskDohD1HpgLW2KmzD1389FY2iiRIFTnOi9T36x1+soluPxUKYyGkSV05qPZ23RugK4jJ8eT++bQZ92hYXd8ZlckXXlZChHXC7JbEmVXE0NgZhjswNCaMTCVGgNYbUVZ03m6ACtkL1AmST3AuNeYDxJ/wFXygKJOV/g29CXmCFBbvEAJ2AJZ+TIF9pRbZpO/AFlbUjpwGwenzfCny9TuuR/KML6fIcalBWfBHsHVd7ykxQtKAprN8Du67rHnh7tTVj3v5LMEs9t29nfnOIdUN3IwhJcIjIo8WHPHHsB8mW5CeQL9eqwAI0ViAXYnekGuZxNxYvmTSTMj/vOKx4W72aYL2IP7DqII25+AA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [ID = _t, #"ID 2" = _t, Name = _t, Value = _t, #"Custom Column" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"ID 2", Int64.Type}, {"Name", type text}, {"Value", type text}, {"Custom Column", type text}}),
 Partition = Table.Group( #"Changed Type" , {"ID","ID 2"}, {{"Partition", each Table.AddIndexColumn(_, "Index",1,1), type table}}),
    #"Expanded Partition" = Table.ExpandTableColumn(Partition, "Partition", {"Name", "Value", "Custom Column", "Index"}, {"Name", "Value", "Custom Column", "Index"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Expanded Partition",{{"Index", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type1", "Custom", each if [ID 2] = null then null else "Survey" &  [Index])
in
    #"Added Custom"

 

Best Regards,

Lin

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

So, could you just create a second query where you pull out all of the distinct values from ID2 and assign an Index to them. That way you should be able to easily relate that dimension table back to this fact table and have what you want, correct?


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.