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
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