Custom column based on several other columns

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?

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

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