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 ne...
Need Help
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!