Alternative to unpivot in powerquery

You could do a lookup via Table.SelectRows.  But it would be better to split the Entries table into separate dimension tables, and then let the data model do the work for you.

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

Hi debjani1111, check also this:

Output

You have to replace code for FactTable and EntriesTable with your tables references. If you don't know how - check note below my post.

v1 Unpivot/Pivot

let
    FactTable = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8k0sUtJRMgTjWJ1oJZfUZBR+cGoBkG0ExrGxAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Census = _t, Accheck = _t, Allct01 = _t]),
    FT_ChangedType = Table.TransformColumnTypes(FactTable,{{"Accheck", Int64.Type}, {"Allct01", Int64.Type}}),
    FT_Unpivoted = Table.UnpivotOtherColumns(FT_ChangedType, {"Census"}, "Attribute", "Value"),
    EntriesTable = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMnRMTs5ITc5W0lFCsCJTi5VidaKVjLBJ+uWD5Qwdc3KSSwwMQXIIVnpRaipUKxZpl8xiqIpYAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [FieldKey = _t, Fieldname = _t, Description = _t]),
    ET_ExtractedKey = Table.TransformColumns(EntriesTable,{{"FieldKey", each Int64.From(List.First(Splitter.SplitTextByCharacterTransition({"0".."9"}, (x)=> not List.Contains({"0".."9"}, x))(_))) , Int64.Type}}),
    MergedQueries = Table.NestedJoin(FT_Unpivoted, {"Value", "Attribute"}, ET_ExtractedKey, {"FieldKey", "Fieldname"}, "Custom1", JoinKind.LeftOuter),
    ExpandedDescription = Table.ExpandTableColumn(MergedQueries, "Custom1", {"Description"}, {"Description"}),
    RemovedColumns = Table.RemoveColumns(ExpandedDescription,{"Value"}),
    PivotedColumn = Table.Pivot(RemovedColumns, List.Distinct(RemovedColumns[Attribute]), "Attribute", "Description")
in
    PivotedColumn

v2 TransformColumns

let
    FactTable = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8k0sUtJRMgTjWJ1oJZfUZBR+cGoBkG0ExrGxAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Census = _t, Accheck = _t, Allct01 = _t]),
    FT_ChangedType = Table.TransformColumnTypes(FactTable,{{"Accheck", Int64.Type}, {"Allct01", Int64.Type}}),
    EntriesTable = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMnRMTs5ITc5W0lFCsCJTi5VidaKVjLBJ+uWD5Qwdc3KSSwwMQXIIVnpRaipUKxZpl8xiqIpYAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [FieldKey = _t, Fieldname = _t, Description = _t]),
    ET_ExtractedKey = Table.TransformColumns(EntriesTable,{{"FieldKey", each Int64.From(List.First(Splitter.SplitTextByCharacterTransition({"0".."9"}, (x)=> not List.Contains({"0".."9"}, x))(_))) , Int64.Type}}),
    ET_GroupedRows = Table.Group(ET_ExtractedKey, {"Fieldname"}, {{"All", each _, type table}}, GroupKind.Local),
    ET_Renamed = Table.RenameColumns(ET_GroupedRows,{{"Fieldname", "Name"}, {"All", "Value"}}),
    ET_Buffered = Table.Buffer(ET_Renamed),
    FinalTable = Table.TransformColumns(FT_ChangedType, List.Transform(List.Skip(Table.ColumnNames(FT_ChangedType)), (r)=> {r, each [
        a = Table.First(Table.SelectRows(ET_Buffered, (x)=> x[Name] = r))[Value],
        b = Table.First(Table.SelectRows(a, (x)=> x[FieldKey] = _))[Description]
      ][b], type text}) )
in
    FinalTable