Creating multiple columns. Use result of Table.SplitColumn to determine cell value

[Anonymous]

Hi,

I have this code:

#"Split Column by Delimiter3" = Table.SplitColumn(#"Reordered Columns" , "Custom2", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), ListB),

Which creates multiple columns from this list with data from the Custom2 column.

 

and this is the result

Now I want to add if the result in each cell is blank or null then  0 (zero) else $200.

so the result word look like

Given that the code creates multiple columns at once what code to i need to wrap around or put inside the existing code for #"Split Column by Delimiter3"?

Any help would be appreciated.

Thanks

[Anonymous]

I've tried to test using List.ReplaceValue(Text.Split([Custom2],","),"0","$200", Replacer.ReplaceText) which almost works by replacing the "0" in eg "S904/01" with "$200" so it reads "S9$2004/01". How would I turn this into For any value replace the whole value with $200

[Anonymous]

Hi Allison,

Thanks for your input. 

The Custom2 column may have 50+ 'values'./ There are 70+ rows with each row having between 1 & 50+ values.

The prior step using the Table.SplitColumn method splits into the 50+ columns automatically.

What I am after is a 'non manual' way of making the result in the cell a predetermined value, say $200, if the prior step for that cell has a 'value' (eg S9962/01) or null/blank/zero if the prior step is a 'blank cell'.

I can do it manually like you suggested but the data varies each time and it would be more efficient to find a 'automated ' way of doing it.

Thanks

[AllisonKennedy]

@Anonymous  What is your final goal? I would guess that you might benefit from an 'unpivot columns' here:

If you need 0 instead of null: - Select all your new columns > Transform > Replace Values > null to 0

Then: - Select your new columns > Transform > unpivot selected columns

Finally: - Select the Add Column > Conditional Column > If [Value] = 0, then 0 otherwise 200

That should get you the ideal data for loading into Power BI. You can do the pivoting in the visuals within the report.

[Anonymous · Accepted solution]

Hi All,

I worked it out! Thanks to Gorilla BI and his YouTube Video "Check if Column Contains Item from List in Power Query - Create Text.ContainsAny!" pointed me in the right direction by using

Table.AddColumn(#"Duplicated Column4", "Custom2", each List.Transform(Text.Split([Custom2prep],","),each "$200")).

The important bits are Custom2 is the column to be split by comma and the items to then be replaced with "$200"

Then the Table.SplitColumn(#"Extracted Values1" , "Custom2", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), ListB)

This made the whole bit dynamic so it doesn't matter if the data need 2 columns or 70 columns saving manual creation of columns