Guidance on IF statement selection PowerBi or PowerBi Switch

[Anonymous]

Good day all, 

I need some guidance. I have a Microsoft Form for HSE Company information with one list of names for all persons no matter which department they are from. All the responses from this form flow to a sharepoint site. The name list has reached the max possiblity of 260 names or so (this cap includes branching throughout the form). I can't duplicate the form per department as there are too many. Instead I plan on using more branching options to seperate the name lists by said departments which for now will work. This edit allows the name lists to be smaller in size depending on the department category selected.

However, I now run into the issue on the PowerBi side whereby I have many different columns for each departments name list. I need to get these seperated columns back into one column for ease of use/visuals  I'd like to know if there's an easy Dax code/query for an "if statement" of sorts whereby a new column is created and will look across all the name listing columns and pull only the column with a name listing selection/entry . This will bring all the names for each submission column into alignment that can then be displayed on one "list" in the visualization. can anyone help me? 

[jbwtp]

Hi @Anonymous,

I think that you may need to do "Unpivot Columns". This will result in a code like this:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjLTt9A3MlLSUXLLzEvMS04FsiAoKDM5uxJIu+YW5ORXpqYqxeogK3etSE0uhqj0yofqQiiNBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, Department = _t, #"BA&L" = _t, Execs = _t, Finance = _t, Type = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Department", type text}, {"BA&L", type text}, {"Execs", type text}, {"Finance", type text}, {"Type", type text}}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Date", "Department", "Type"}, "Attribute", "Value")
in
    #"Unpivoted Columns"

Then you can filter out nulls. For 260 odd lines it should not be too slow.

Kind regards,

John