Alternative to unpivot in powerquery

[debjani1111]

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 unpivoting Accheck, Allct01 and Allct02 ?

Fact table:

Census   Accheck  Allct01                                           

  Mar          1              1             

  Dec          1              1             

  Sep          2              2             

Entries:

FieldKey           Fieldname     Description

1Accheck         Accheck         Yes

2Accheck         Accheck         No

1Allct01           Allct01          Agree 

2Allct01           Allct01          DisAgree 

Desired output:

Census   Actcheck  Allact01                                   

  Mar          Yes        Agree       

  Dec          Yes        DisAgree 

  Sep           No             0    

[dufoq3]

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

[v-heq-msft]

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]

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

[v-heq-msft]

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

[lbendlin]

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.

[debjani1111]

Thanks a lot for your reply.However, I have many columns in Entries table, so that would mean so many dimension tables, so not very sure if there is any other way rather than unpivot?