Forum Discussion

mcantos's avatar
mcantos
Helper II
2 years ago

Create a new table from columns in another table usign calculations

Hello!


First of all, I'm starting to work on Power Query. I need some guidelines because I tried to figure them out and I just couldn't. 

I have this table, well, it is a big table, but I only need these two fields that belong to the same table "Opportunity":

 

 

And from that table, I need to get something like this:

 

 

 

I don't know what is the best way to do it whether is by creating a new table or measures.

Thank you so much in advance.

Need Help

2 Replies

  • Vijay_A_Verma's avatar
    Vijay_A_Verma
    Most Valuable Professional
    2 years ago

    See a sample code below (last 2 steps are for interest to you. Basically add a Custom colum with value of 1 and then pivot it.

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTIyMDJSitWJVnJC5qDIOEM4xgQ4jlg4JkqxsQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Current stage" = _t, Year2 = _t]),
    #"Added Custom" = Table.AddColumn(Source, "Custom", each 1),
    #"Pivoted Column" = Table.Pivot(#"Added Custom", List.Distinct(#"Added Custom"[Year2]), "Year2", "Custom", (x)=>List.Min({1,List.Count(x)}))
in
    #"Pivoted Column"

     

    • mcantos's avatar
      mcantos
      Helper II
      2 years ago

      Hi Vijay_A_Verma ,

      Thank you for your quick reply. 

      I couldn't find a way to do what you explained. I tried to mimic the syntaxis to create the columns but the editor says it is not correct and I think is because I am using a DirectQuery connection and I pretend to transform the data in the Model view. 

       

       

      Does that make any sense to you? Sorry, I'm really new to this. 

      Thank you!