Alternative to unpivot in powerquery

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