Forum Discussion

alexdr's avatar
alexdr
Frequent Visitor
6 years ago
Solved

Help with Table conversion

Hi Everyone,

Thank you for the help to start with!

 

I am trying to transform simple 2 column table, to use Distinct Values from 2nd column to be new columns and their headers,

and all the corresponding values from 1st column to be showing as row values on those new Columns:

 

Orig Table:

 

Product ID       Store Key

abc                   AM

cba                   AM

abe                   DC

cgr                    DC

 

and so on:

 

I need to transform it to be:

 

AM                  DC

abc                   abe

cba                   cgr

 

I tried grouping/Pivoting, but nothing really works.

any ideas?

  • ImkeF's avatar
    ImkeF
    6 years ago

    Hi alexdr ,

    I'm providing a solution below that covers your request, but like to add warning: The table you're requesting here is not suitable for a data model. The nature of the Power BI (or Power Pivot) data models assume that the items/fields/columns of the rows/records of your table have a relation / belong to each other. 

     

    Please paste the code below in the advanced editor and follow the steps.

     

    It groups the table on "Store Key" and separates out all rows for each Store Key.

    Then it grabs just the "Product ID"-values and creates a new table from it (Table.FromColumns) where the values are arranged columns-wise.

     

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUXL0VYrViVYyQjCNEUwTBNMUwTRDMEEmuDjDtUGZplBmLAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Product ID" = _t, #"Store Key" = _t]),
    GroupOnStoreIdToSeparateOutValues = Table.Group(Source, {"Store Key"}, {{"All Rows", each _, type table [Product ID=text, Store Key=text]}}),
    SelectJustProductIDColumn = Table.AddColumn(GroupOnStoreIdToSeparateOutValues, "Product ID Columns", each [All Rows][Product ID]),
    ArrangeColumnWise = Table.FromColumns( SelectJustProductIDColumn[Product ID Columns], SelectJustProductIDColumn[Store Key] )
in
    ArrangeColumnWise

     

     

5 Replies

    • ImkeF's avatar
      ImkeF
      Community Champion
      6 years ago

      Hi alexdr 

      this looks like a very simplyfied description of your problem, but it looks you're missing a row identifier here.

      Is there actually a relation between AM: abc and DC: abe or do they just coincidently appear a the first position?
      Could you also have a situation where there are more (or less) DC items than AM items?

       

      • alexdr's avatar
        alexdr
        Frequent Visitor
        6 years ago

        hi ImkeF 

        Thank you for trying to help.

        Really new to "M", so not really sure what I need to do.

        I hoped I will be able to pivot one column from first table, and it will do the trick, but it gives me error, in case I choose not Aggregate values.

        so here is what I am trying to achieve:

         

        Current Table:

         

        Product ID    |    Store Key

        1                           AM

        2                           AM

        3                           AM

        4                           AM

        5                           AM

        6                           AM

        1                           DC

        3                           DC

        5                           DC

         

        Transformed Table Needed:

        AM        |            DC

        1                           1

        2                           3

        3                           5

        4

        5

        6

         

        As you can see, there are exactly same values and their amounts in both tables,

        I just changed the appearance of those.

        There will definitely be duplicates for Product ID, but those duplicates will always have different Store Key in each of those.

         

        For example in our case 1 / 3 / 5 Product IDs are appearing twice in original dataset, as sames IDs are used in both stores.