Forum Discussion

tgjones43's avatar
tgjones43
Icon for Helper IV rankHelper IV
7 years ago

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 th...
Need Help
v-lili6-msft's avatar
v-lili6-msft
Icon for Community Support rankCommunity Support
7 years ago

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:

Step2:

Filter null value for ID2 and remove Count column

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

 

 

 

Step4:

Merge the tables and expand the column

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:

Here is pbix file, please try it.

 

Best Regards,

Lin

 

 

 

 

 

 

 

 

 

tgjones43's avatar
tgjones43
Icon for Helper IV rankHelper IV
7 years ago

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. 

  • v-lili6-msft's avatar
    v-lili6-msft
    Icon for Community Support rankCommunity Support
    7 years ago

    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