How do I split a column according to another column?

[KGuyton]

I have this table here

I am trying to split the "config_customfield" column into two columns using "Est. Renew $" and "Renewal Term" as column headers and "User_Defined_Field_Value" as the value of the column.  Whats the easiest way to do this?

Thank you.

[KGuyton]

I got this far but now I need to somehow condense the columns according to config_recid so that the null values aren't there.

I used the Conditional Column function but I dont  think this method will allow me to remove the null values

[Anonymous]

Hi @KGuyton ,

Tring to understand your requirements.

Do you want to replace Null with the values from the config_recid col?

Thanks,

Tejaswi

[KGuyton]

No,  referring to the original post.  I need to split the config_customfield column.  I need my table to look like this.

[Anonymous]

Hi @KGuyton ,

And from where is your this data coming from? I dont see i in your screenshots.

Thanks,

Tejaswi

[KGuyton]

thats randomly inserted data into excel.  other configs not in the original post have different numerical values.  I was expressing this condition.  Each configuration is denoted by a RecID and will have an Est. Renew $ value as well as a Renewal Term.  There are ony 5 options for Renewal Term 0-5 years.  It is also possible to have duplicate Est. Renew values but they are irrelevant to each other

[Anonymous]

Hi @KGuyton ,

Would it be possible to provide some sample data in excel?

I want to recreate the issue first.

Thanks,

Tejaswi

[KGuyton]

Here you go.

http://s000.tinyupload.com/?file_id=70991167215530771059

[Anonymous]

Hi @KGuyton ,

Do you want somethign like this?

Thanks,

Tejaswi

[KGuyton]

where did the duplicates come from and the values don't match the original data (Config RecID 111 should be 1 for renewal term and the Est Renew $ should be blank.)

[Anonymous]

Hi @KGuyton ,

My first step was to Pivot the data.

After pivoting I got nulls. ( looking at your post seems like you ghad already achieved this step)

I tried to fill the nulls value by using the fill up option. I did this because I dont want any other data to be missed.

May be I  misunderstood your requirements.

For the given sample data how would you want your output to look like?   this will provide me enough insights.

Thanks,

Tejaswi

[KGuyton]

I need the output to look like this but with correct values

[Anonymous]

Hi @KGuyton ,

It still looks unclear to me.

Looking at your output seems like you are missing bunch of other data too.( not sure)

I would also love to see the solutions. So tagging the experts @v-lili6-msft, @v-xuding-msft,@v-cherch-msft

Thanks,

Tejaswi

[KGuyton]

Yes data is missing,  that was merely a visual representation on what I need the table to look like.  Essentially I just need those 3 columns with the appropriate data.  I believe I need to create a native query selecting that table but I am a novice with SQL syntax.  This is what I wrote end of day yesterday but I am returning an error that states "Microsoft SQL: Subquery returned more than 1 value.  This is not permitted when the subquery follows =, !=,...."

Code:

Select Config_RecID
, (select User_Defined_field_Value AS 'Est. Renew $'
FROM v_rpt_ConfigCustomFields
WHERE User_Defined_Field_RecID in (4))
, (select User_Defined_field_Value AS 'Renewal Term'
FROM v_rpt_ConfigCustomFields
WHERE User_Defined_Field_RecID in (5))
FROM v_rpt_ConfigCustomFields

[HotChilli]

Pivot the config_customfield column. Use the other column in Values.

Power Query->Transform tab.

[KGuyton]

I tried that first and I only have the option to unpivot which leaves me with a value column identical to the original