Help with Table conversion

[alexdr]

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?

[Greg_Deckler]

@ImkeF @edhans

[ImkeF]

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]

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.

[ImkeF · Accepted solution]

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

[alexdr]

Hi @ImkeF,

Thank you so much for the help.

I had a bit of tough time transform it in my advanced editor, as having related source, I needed to change.

but with a bit of tweaking it worked! Thank you so much!

I am pretty sure, you were correct and it won't work, as I won't be able to address many relationships to this table due to the way Power BI works, but it is still really nice to see how it works!

I am wishing you a nice day ahead, and hope you and your family will all stay safe during this time of uncertaincy!

Kind Regards,

Alex