Forum Discussion
Help with Table conversion
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
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.
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
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