Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
sebsenciel
Frequent Visitor

POWERBI TRANSFORM TABLE TO AN OTHER

Hello,

I'd like to be able to transform table 1 into 2 from the settings table.
Purpose: duplicate the rows of table 1 in order to have all the indices of CCT1 column (here between 1 and 6), which gives table 2.

sebsenciel_0-1701362044965.png

 

 

Thank's a lot

 

1 ACCEPTED SOLUTION
Xunh
Regular Visitor

Hi,
it seems that what you want to achieve is basically a cross join parameter table. As in merge there's no cross join the way to achieve this is using a custom column referencing the second table as you can see here:
Cross join - Power Query | Microsoft Learn
Now in your case what you can do to achieve a similar scenario is to create a parameter, for instance maxInt.
Then add a blank query choose your favorite name for instance MyParameterTable and use this M language snippet

let
     MyParameterQuery = (MaxInt) => List.Numbers(1,MaxInt)
in
     MyParameterQuery

Invoking the function providing parameter value will create the list of value that you need. Default name is Invoked function but you can rename as you prefer. Once you're done with this you can rely on custom column and then expand rows as in following snippet. 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUXIBYnelWJ1oJScgyxWIPcA8ZyDLDYg9lWJjAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Col1 = _t, Col2 = _t, Col3 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Col1", type text}, {"Col2", type text}, {"Col3", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each #"Invoked Function"),
    #"Renamed Columns" = Table.RenameColumns(#"Added Custom",{{"Custom", "Param"}}),
    #"Expanded Param" = Table.ExpandListColumn(#"Renamed Columns", "Param")
in
    #"Expanded Param"

Hope it helped!


View solution in original post

3 REPLIES 3
Xunh
Regular Visitor

Hi,
it seems that what you want to achieve is basically a cross join parameter table. As in merge there's no cross join the way to achieve this is using a custom column referencing the second table as you can see here:
Cross join - Power Query | Microsoft Learn
Now in your case what you can do to achieve a similar scenario is to create a parameter, for instance maxInt.
Then add a blank query choose your favorite name for instance MyParameterTable and use this M language snippet

let
     MyParameterQuery = (MaxInt) => List.Numbers(1,MaxInt)
in
     MyParameterQuery

Invoking the function providing parameter value will create the list of value that you need. Default name is Invoked function but you can rename as you prefer. Once you're done with this you can rely on custom column and then expand rows as in following snippet. 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUXIBYnelWJ1oJScgyxWIPcA8ZyDLDYg9lWJjAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Col1 = _t, Col2 = _t, Col3 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Col1", type text}, {"Col2", type text}, {"Col3", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each #"Invoked Function"),
    #"Renamed Columns" = Table.RenameColumns(#"Added Custom",{{"Custom", "Param"}}),
    #"Expanded Param" = Table.ExpandListColumn(#"Renamed Columns", "Param")
in
    #"Expanded Param"

Hope it helped!


Hello Xunh,
Thank you for your reply... BRILLIANT reply, in fact 🙂
Much appreciated
Thank you from the bottom of my heart
Thanks again
Have a great day

Glad it helped 🙂
Have a nice weekend!

Helpful resources

Announcements
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.