Forum Discussion

FilipK's avatar
FilipK
Resolver I
5 years ago
Solved

Pivot column with two value columns (condition)

The task

I want to pivot this dataset:

 

namevalueDoubleMaxvalueStringMaxepoch_time
f121null1,60551E+12
f2nullCDE65563-CPX11,60551E+12
f399null1,60551E+12
f492null1,60551E+12
f537594612null1,60551E+12
f6-5129null1,60551E+12
f736938751null1,60551E+12
f8-19null1,60551E+12
f982null1,60551E+12
f1099null1,60552E+12
f1193null1,60552E+12
f1237597793null1,60552E+12
f13-5137null1,60552E+12
f1436941932null1,60552E+12
f15-14null1,60552E+12

 

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

Desired output

epochtimef1f2f3f4f5f6f7f8f9f10f11f12f13f14f15
1,61E+1221CDE65563-CPX1999237594612-512936938751-1982999337597793-513736941932-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?
Need Help

7 Replies

  • CNENFRNL's avatar
    CNENFRNL
    Community Champion
    5 years ago

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

     

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

     

    • FilipK's avatar
      FilipK
      Resolver I
      5 years ago

      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's avatar
        v-yingjl
        Community Support
        5 years ago

        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.

  • mahoneypat's avatar
    mahoneypat
    Microsoft Employee
    5 years ago

    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