Forum Discussion

debjani1111's avatar
debjani1111
Frequent Visitor
1 year ago

Alternative to unpivot in powerquery

Hi Everyone,   I have two tables which has many columns and rows, so just posting really a sample of it. Is there any way to get the Description column values from the Entries table without unpivot...
Need Help
Anonymous's avatar
Anonymous
Not applicable
1 year ago

Hi debjani1111 ,
Thanks for lbendlin reply. You can check the following code.

let
    Fact = Table.FromRecords({
        [Census = "Mar", Accheck = 1, Allct01 = 1],
        [Census = "Dec", Accheck = 1, Allct01 = 2],
        [Census = "Sep", Accheck = 2, Allct01 = 2]
    }),
    Entries = Table.FromRecords({
        [FieldKey = 1, Fieldname = "Accheck", Description = "Yes"],
        [FieldKey = 2, Fieldname = "Accheck", Description = "No"],
        [FieldKey = 1, Fieldname = "Allct01", Description = "Agree"],
        [FieldKey = 2, Fieldname = "Allct01", Description = "DisAgree"]
    }),
    AccheckMapping = Table.SelectRows(Entries, each [Fieldname] = "Accheck"),
    Allct01Mapping = Table.SelectRows(Entries, each [Fieldname] = "Allct01"),
    FactWithAccheck = Table.NestedJoin(Fact, {"Accheck"}, AccheckMapping, {"FieldKey"}, "AccheckMapping", JoinKind.LeftOuter),
    FactExpandedAccheck = Table.ExpandTableColumn(FactWithAccheck, "AccheckMapping", {"Description"}, {"AccheckDescription"}),
    FactWithAllct01 = Table.NestedJoin(FactExpandedAccheck, {"Allct01"}, Allct01Mapping, {"FieldKey"}, "Allct01Mapping", JoinKind.LeftOuter),
    FactExpandedAllct01 = Table.ExpandTableColumn(FactWithAllct01, "Allct01Mapping", {"Description"}, {"Allct01Description"}),
    Result = Table.SelectColumns(FactExpandedAllct01, {"Census", "AccheckDescription", "Allct01Description"}),
    RenamedResult = Table.RenameColumns(Result, {{"AccheckDescription", "Accheck"}, {"Allct01Description", "Allct01"}}),
    FinalResult = Table.ReplaceValue(RenamedResult, null, "0", Replacer.ReplaceValue, {"Accheck", "Allct01"})
in
    FinalResult

Final output

 

Best regards,
Albert He

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

 

debjani1111's avatar
debjani1111
Frequent Visitor
1 year ago

Hi, many thanks for your reply. However, these tabless have huge data with large no.of columns more than 50, so it is impossible for me to hardcode like this

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    Hi debjani1111 ,
    Hardcoding is really not a good method if there are many columns, but the need to determine the matching relationship between columns and the fastest way to convert row logic to column logic is still pivot and unpivot.

     

    Best regards,
    Albert He

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly