Pivot column with two value columns (condition)

[FilipK]

The task

I want to pivot this dataset:

name	valueDoubleMax	valueStringMax	epoch_time
f1	21	null	1,60551E+12
f2	null	CDE65563-CPX1	1,60551E+12
f3	99	null	1,60551E+12
f4	92	null	1,60551E+12
f5	37594612	null	1,60551E+12
f6	-5129	null	1,60551E+12
f7	36938751	null	1,60551E+12
f8	-19	null	1,60551E+12
f9	82	null	1,60551E+12
f10	99	null	1,60552E+12
f11	93	null	1,60552E+12
f12	37597793	null	1,60552E+12
f13	-5137	null	1,60552E+12
f14	36941932	null	1,60552E+12
f15	-14	null	1,60552E+12

Whenever valueDoubleMax=null the other column shall be used as value column in pivot context.

Desired output

epochtime

f1

f2

f3

f4

f5

f6

f7

f8

f9

f10

f11

f12

f13

f14

f15

1,61E+12

21

CDE65563-CPX1

99

92

37594612

-5129

36938751

-19

82

99

93

37597793

-5137

36941932

-14

…

…

…

…

…

…

…

…

…

…

…

…

…

…

…

…

Problem

Since pivot column function in power query editor only supports one column I wonder how to do it.

Ideas

In SQL the approach would be the following:

select * from (

  select epoch_time, max(CASE WHEN name = 'f1' THEN value_string END) f1

...

No options

I don't want to use SQL statement at query start, since I need to run an incremental refresh later on. SQL view is also not wished.

Also a merge into one string column is not desired (dataset size)

What is your recommendation?

[CNENFRNL]

@FilipK , you might want to combine those 2 columns this way,

#"Column Combined" = Table.AddColumn(#"Previous Step", "Custom", each (Text.From([valueDoubleMax]) ?? "") & ([valueStringMax] ?? ""))

[FilipK]

@CNENFRNL , that's nice. I didn't notice that this is possible. The solution has one drawback, for it appears that it's not able to be folded. Is there any other possibility?

[v-yingjl]

Hi @FilipK ,

Seems like you are using Direct Query mode to connect to your data source. If you want to create the new column and see te Native Query at the same time, you can try these steps:

1. Change the data type of [valueDoubleMax] from number to text

2. Replace the null value in [valueDoubleMax] and [valueStringMax] from null to ""(Blank)

3. Add a custom column to combine these two columns

= Table.AddColumn(#"Replaced Value1", "Custom", each [valueDoubleMax] & [valueStringMax])

 

Now you can check the native query and it should work, you can also use this column to pivot columns as your need.

In conclusion, the whole query may look like this:

let
    Source = Sql.Databases("xxx"),
    xxxx = Source{[Name="xxxx"]}[Data],
    dbo_Pivot_table = xxxx{[Schema="dbo",Item="Pivot_table"]}[Data],
    #"Changed Type" = Table.TransformColumnTypes(dbo_Pivot_table,{{"valueDoubleMax", type text}}),
    #"Replaced Value" = Table.ReplaceValue(#"Changed Type",null,"",Replacer.ReplaceValue,{"valueDoubleMax"}),
    #"Replaced Value1" = Table.ReplaceValue(#"Replaced Value",null,"",Replacer.ReplaceValue,{"valueStringMax"}),
    #"Added Custom" = Table.AddColumn(#"Replaced Value1", "Custom", each [valueDoubleMax] & [valueStringMax])
in
    #"Added Custom"

Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[FilipK]

Hey @v-yingjl . Thanks for your recommondation. I've already thought about it and came to the clue that this is not possible for typechange is not allowed for folding/ incremental refresh. Am I wrong? If so, it could be an option even I'm afraid of increasing the dataset a lot (number -> string). 

Direct query is not used.

[v-yingjl · Accepted solution]

Hi @FilipK ,

For native query, when you changed data type, the folding will be triggered and you can see it.

For incremental refresh, the data type of refresh parameter can only be the date type.

You can refer this blog about incremental refresh: Keep The Existing Data In Your Power BI Dataset And Add New Data To It Using Incremental Refresh 

Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[mahoneypat]

Please show your desired output table.  Not sure which column you want to pivot on (i.e., which should be the column headers).

Regards,

Pat

[FilipK]

Original post updated.