Forum Discussion

joshua1990's avatar
joshua1990
Icon for Post Prodigy rankPost Prodigy
5 years ago
Solved

Table with individual columns with numerical keys and a table with the corresponding description

Hey guys!

I have a table (Global) that has this structure

Article Key 1Key 2Key 3Key 4Key 5Key 6Key 7 Key 8Key 9
Metal010188650777ACBIKL

 

Then I have a second table (Description) this structure:

TableFilterKeyDescription
GlobalAA01Aluminum
Global AA02Copper
GlobalBB011m
GlobalC1882mm

 

This table contains the specific Information/ description to the corresponding key. For every key exists a specific filter for bot columns: Table and Filter.

The goal is to expand the table "Global" with the description for every key.

The way I am following now is to create several tables of Description to have specific tables for every key. These tables will be then merged to the table Global.

But this approach would create numerous tables.

Is there maybe a simpler way? 

Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago

    I would transpose the Key table, and then merge it to the Global table.  But it's important to first make sure that all of the columns are of type text.  Also, you must first demote the headers before transposing, or you will lose data.

     

    let
    Source = Excel.CurrentWorkbook(){[Name="KeyTable"]}[Content],
    #"Demoted Headers" = Table.DemoteHeaders(Source),
    #"Transposed Table" = Table.Transpose(#"Demoted Headers"),
    #"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table", [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Metal", type text}, {"Article ", type text}}),
    #"Merged Queries" = Table.NestedJoin(#"Changed Type", {"Metal"}, GlobalTable, {"Key"}, "NewKeyTable", JoinKind.LeftOuter),
    #"Expanded NewKeyTable" = Table.ExpandTableColumn(#"Merged Queries", "NewKeyTable", {"Filter", "Key", "Description"}, {"Filter", "Key", "Description"})
    in
    #"Expanded NewKeyTable"

     

    --Nate

     

     

3 Replies

  • ImkeF's avatar
    ImkeF
    Icon for Community Champion rankCommunity Champion
    5 years ago

    Hi joshua1990 ,

    I'm not sure I understand your description and it is difficult without seeing the desired result anyway.
    But your Global table looks like it needs an unpivot-other columns on "Article".
    Then you could merge with a list of lookup tables and pivot back if needed.

     

  • v-kelly-msft's avatar
    v-kelly-msft
    Icon for Community Support rankCommunity Support
    5 years ago

    Hi  joshua1990 ,

     

    Could you pls provide an expected output for us to test?

     

    Best Regards,
    Kelly

    Did I answer your question? Mark my post as a solution!

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    I would transpose the Key table, and then merge it to the Global table.  But it's important to first make sure that all of the columns are of type text.  Also, you must first demote the headers before transposing, or you will lose data.

     

    let
    Source = Excel.CurrentWorkbook(){[Name="KeyTable"]}[Content],
    #"Demoted Headers" = Table.DemoteHeaders(Source),
    #"Transposed Table" = Table.Transpose(#"Demoted Headers"),
    #"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table", [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Metal", type text}, {"Article ", type text}}),
    #"Merged Queries" = Table.NestedJoin(#"Changed Type", {"Metal"}, GlobalTable, {"Key"}, "NewKeyTable", JoinKind.LeftOuter),
    #"Expanded NewKeyTable" = Table.ExpandTableColumn(#"Merged Queries", "NewKeyTable", {"Filter", "Key", "Description"}, {"Filter", "Key", "Description"})
    in
    #"Expanded NewKeyTable"

     

    --Nate