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

     

     

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!

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

    Hi afaherty ,

    Thank you for the update. As per your requirement i have provided the solution. If you have new requirement , please create a new thread based on your requirement in Fabric community. Our community members will assit on it.

     

    Regards,

    Dinesh

     

     

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

    Hi afaherty ,

    We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. And, if you have any further query do let us know.

     

    Regards,

    Dinesh

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

    Hi @afaherty ,

    We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. And, if you have any further query do let us know.

     

    Regards,

    Dinesh

  • afaherty's avatar
    afaherty
    Helper V
    10 months ago

    v-dineshya Hello, sorry, I just didn't want to make a whole separate post asking how to nest 2 "let"s/"in"s.

  • v-dineshya's avatar
    v-dineshya
    Community Support
    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

     

     

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

    Hi @afaherty ,

    We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. And, if you have any further query do let us know.

     

    Regards,

    Dinesh