Forum Discussion

afaherty's avatar
afaherty
Helper V
11 months ago
Solved

Pivoting only working sometimes

Hello All, I have spent hours searching for a solution to this and I am surprised I haven't found one! I have this fake data here BUT it is important to note that my real data has many more columns...
Need Help
  • v-dineshya's avatar
    v-dineshya
    10 months ago

    Hi afaherty ,

     

    Please refer below M code.

     

    let
    Source = Excel.Workbook(File.Contents("C:\Users\v-dineshya\Downloads\sample.xlsx"), true),
    Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
    PromotedHeaders = Table.PromoteHeaders(Sheet, [PromoteAllScalars=true]),
    RenamedCols = Table.RenameColumns(
    PromotedHeaders,
    {
    {Table.ColumnNames(PromotedHeaders){0}, "StudentID"},
    {Table.ColumnNames(PromotedHeaders){1}, "Test"},
    {Table.ColumnNames(PromotedHeaders){2}, "Score"}
    }
    ),

    AsText = Table.TransformColumnTypes(
    RenamedCols,
    {{"StudentID", Int64.Type}, {"Test", type text}, {"Score", type text}}
    ),

    Filtered = Table.SelectRows(
    AsText,
    each [Score] <> null and Text.Trim([Score]) <> "" and Text.Upper(Text.Trim([Score])) <> "N/A"
    ),

    WithNumber = Table.TransformColumns(
    Filtered,
    {{"Score", each try Number.From(_) otherwise null, type nullable number}}
    ),

    Pivoted = Table.Pivot(
    WithNumber,
    List.Distinct(WithNumber[Test]),
    "Test",
    "Score",
    List.Max
    )
    in
    Pivoted

     

    Please refer below sample data , M code , output snap and attached PBIX file.

     

     

     

     

     

    I hope this information helps. Please do let us know if you have any further queries.

     

    Regards,

    Dinesh

     

     

afaherty's avatar
afaherty
Helper V
11 months ago

v-dineshya I was able to download your file. Forgive me, but I am just a bit confused. I do see your M code but I can't type out every student's test & scores. Apologies if I am misunderstanding! Thanks so much!

v-dineshya's avatar
v-dineshya
Community Support
11 months ago

Hi afaherty ,

Thank you for the update. Could you please confirm that, from which data source you are pulling the data , For example from excel, csv , SQL server, share point or another api, based on the data source i will change the M code. 

 

Regards,

Dinesh

  • afaherty's avatar
    afaherty
    Helper V
    11 months ago

    v-dineshya Sure, I import from an Excel file. Thanks!

    • v-dineshya's avatar
      v-dineshya
      Community Support
      11 months ago

      Hi afaherty ,

      Please refer below updated M code. I took sample excel, i have imported excel data into Power bi.

       

      let
      Source = Excel.Workbook(File.Contents("C:\Users\v-dineshya\Downloads\sample.xlsx"), true),
      Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
      PromotedHeaders = Table.PromoteHeaders(Sheet, [PromoteAllScalars=true]),
      RenamedCols = Table.RenameColumns(
      PromotedHeaders,
      {
      {Table.ColumnNames(PromotedHeaders){0}, "StudentID"},
      {Table.ColumnNames(PromotedHeaders){1}, "Test"},
      {Table.ColumnNames(PromotedHeaders){2}, "Score"}
      }
      ),

      ChangeType = Table.TransformColumnTypes(
      RenamedCols,
      {{"StudentID", Int64.Type}, {"Test", type text}, {"Score", Int64.Type}}
      ),

      Pivoted = Table.Pivot(
      ChangeType,
      List.Distinct(ChangeType[Test]),
      "Test",
      "Score",
      List.Max
      )
      in
      Pivoted

       

      Please refer output snap.

       

      Please replace above file path with your excel file path.  Please refer output snap and attached PBIX file.

       

       

      I hope this information helps. Please do let us know if you have any further queries.

       

      Regards,

      Dinesh

       

      • afaherty's avatar
        afaherty
        Helper V
        11 months ago

        v-dineshya Thank you! I do have one step that occurs before your M code, and that is to filter out any scores of null, blank, or N/A. I am still a newbie to M, so I am not sure how to incorporate my filtering step along with your M code above. Thanks again!