If statement - multiple actions

[jthomson]

Hi,

I'm trying to clean up some data that I'm working on - basically I have multiple columns which are a mixture of nulls and data, which I want to compress down like so:

Basically making answer 1 the first entry in the series of answer columns that isn't null, answer 2 the second entry that isn't null, etc etc. I'm trying to do this in M, and one way I've thought to do this and (and have got working) is to make a new custom column that's a bunch of nested IF statements that look at each column and if it's not null pull through the value, otherwise look at the next one. This works fine for the first column, but for the second I'd need to ignore the first non-null answer. The easiest way I can think of would be to look for the first non-null as described, but then at the same time make the original column it's looking at null as well, so that I can then repeat the process for future columns. Any thoughts on how I could make this work, or alternative methods I could use (I don't know if for future columns I could add a variable that adds 1 each time it finds a non-null and only pull the value through to the new column once the variable is the right size)? I'm open to a DAX solution instead if that'd be easier.

[stretcharm · Accepted solution]

One way would be to use pivot and unpivot.

Basically

Add an RowKey index (unless you have a key for the rows)

unpivot the answers

Remove the blanks

Group and Rank Rows by RowKey

Unpivot by a a Column based on Rank

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcspPUtJRAiKvzFwg6ZJYlqoUqxMNEoFIuSSWpIJUgAQhauBSSrGxAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Answer1 = _t, Answer2 = _t, Answer3 = _t, Answer4 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Answer1", type text}, {"Answer2", type text}, {"Answer3", type text}, {"Answer4", type text}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Added Index", {"Index"}, "Attribute", "Value"),
    #"Filtered Rows" = Table.SelectRows(#"Unpivoted Columns", each ([Value] <> "")),
    #"Grouped Rows" = Table.Group(#"Filtered Rows", {"Index"}, {{"AllRows", each _, type table}}),
    #"Invoked Custom Function" = Table.AddColumn(#"Grouped Rows", "Rows", each fnRankTable([AllRows])),
    #"Expanded Rows" = Table.ExpandTableColumn(#"Invoked Custom Function", "Rows", {"Attribute", "Value", "Index.1"}, {"Rows.Attribute", "Rows.Value", "Rows.Index.1"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded Rows",{"AllRows"}),
    #"Added Custom" = Table.AddColumn(#"Removed Columns", "Answer", each "Answer" & Number.ToText([Rows.Index.1])),
    #"Removed Columns1" = Table.RemoveColumns(#"Added Custom",{"Rows.Attribute", "Rows.Index.1"}),
    #"Pivoted Column" = Table.Pivot(#"Removed Columns1", List.Distinct(#"Removed Columns1"[Answer]), "Answer", "Rows.Value")
in
    #"Pivoted Column"

fnRankTable

let
    Source = (SourceTable as table) => let
        #"Sorted Rows" = Table.Sort(SourceTable,{{"Attribute", Order.Ascending}}),
        #"Added Index1" = Table.AddIndexColumn(#"Sorted Rows", "Index.1", 0, 1)
    in
        #"Added Index1"
in
    Source

Another option is to merge the answers with a delimiter.

Remove starting and duplicate delimiters, then split it again.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcspPUtJRAiKvzFwg6ZJYlqoUqxMNEoFIuSSWpIJUgAQhauBSSrGxAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Answer1 = _t, Answer2 = _t, Answer3 = _t, Answer4 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Answer1", type text}, {"Answer2", type text}, {"Answer3", type text}, {"Answer4", type text}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1),
    #"Merged Columns" = Table.CombineColumns(#"Added Index",{"Answer1", "Answer2", "Answer3", "Answer4"},Combiner.CombineTextByDelimiter("|", QuoteStyle.None),"Merged"),
    #"Replaced Value" = Table.ReplaceValue(#"Merged Columns","||","|",Replacer.ReplaceText,{"Merged"}),
    #"Replaced Value1" = Table.ReplaceValue(#"Replaced Value","||","|",Replacer.ReplaceText,{"Merged"}),
    #"Added Custom" = Table.AddColumn(#"Replaced Value1", "Answers", each Text.TrimStart([Merged],"|")),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Merged"}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Removed Columns", "Answers", Splitter.SplitTextByDelimiter("|", QuoteStyle.Csv), {"Answers.1", "Answers.2", "Answers.3"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Answers.1", type text}, {"Answers.2", type text}, {"Answers.3", type text}})
in
    #"Changed Type1"

[jthomson]

Cheers for the reply - have gone for the splitting by delimiter option which seems to be working fine. I may need to look into how the end visualisations etc work as the final number of columns may vary depending on the data that we get from week to week, so if my visual's expecting five columns and the data only produces four it might break, but that's for a different question I think