Forum Discussion

DGPBi's avatar
DGPBi
Helper I
2 years ago
Solved

Create a dynamic table based on some colums from another table

Hello everyone, I'm trying to create a dynamic table based on some columns from another table. The goal is to convert columns data to rows. I did it with DAX but also can be done with Power Query M...
Need Help
  • DGPBi's avatar
    DGPBi
    2 years ago

    Hi Manuel,
    Thank you for your quick answer. 
    I was more or less able to reproduce your code. Where I'm still challenged is about the column: "Role" which was created from text field. In fact this field come from the column name: BRM, BusinessContact, etc ... from the other Table.
    So, at the moment I don't know how to add this info.

     

    Roles = 
VAR T1 = SELECTCOLUMNS(LocationList, "ITNL", LocationList[Title],"PlanonID", LocationList[PlanonID], "CompanyCode",LocationList[CompanyCode],"OBJ_CompCode",LocationList[OBJ_CompCode],"Role", "BRM", "UserID", LocationList[Site BRM contactId])

     

     

    let
    Source = LocationList,
    #"Removed Other Columns" = Table.SelectColumns(Source,{"Title", "PlanonID", "CompanyCode", "Site BRM contactId", "OBJ_CompCode"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Other Columns",{{"Site BRM contactId", "UserID"}, {"Title", "ITNL"}}),
    Split = Table.SplitColumn(#"Renamed Columns", "UserID", Splitter.SplitTextByDelimiter(";", QuoteStyle.Csv), {"UserID.1", "UserID.2", "UserID.3"}),
    Type = Table.TransformColumnTypes(Split,{{"UserID.1", Int64.Type}, {"UserID.2", Int64.Type}, {"UserID.3", Int64.Type}}),
    Unpivot = Table.UnpivotOtherColumns(Type, {"ITNL", "PlanonID", "CompanyCode", "OBJ_CompCode", "Role"}, "Attribut", "UserID"),
    Columns = Table.SelectColumns(Unpivot,{"ITNL", "PlanonID", "CompanyCode", "OBJ_CompCode", "Role", "UserID"})
in
    Columns
DGPBi's avatar
DGPBi
Helper I
2 years ago

Hi Manuel,
Thank you for your quick answer. 
I was more or less able to reproduce your code. Where I'm still challenged is about the column: "Role" which was created from text field. In fact this field come from the column name: BRM, BusinessContact, etc ... from the other Table.
So, at the moment I don't know how to add this info.

 

Roles = 
VAR T1 = SELECTCOLUMNS(LocationList, "ITNL", LocationList[Title],"PlanonID", LocationList[PlanonID], "CompanyCode",LocationList[CompanyCode],"OBJ_CompCode",LocationList[OBJ_CompCode],"Role", "BRM", "UserID", LocationList[Site BRM contactId])

 

 

let
    Source = LocationList,
    #"Removed Other Columns" = Table.SelectColumns(Source,{"Title", "PlanonID", "CompanyCode", "Site BRM contactId", "OBJ_CompCode"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Other Columns",{{"Site BRM contactId", "UserID"}, {"Title", "ITNL"}}),
    Split = Table.SplitColumn(#"Renamed Columns", "UserID", Splitter.SplitTextByDelimiter(";", QuoteStyle.Csv), {"UserID.1", "UserID.2", "UserID.3"}),
    Type = Table.TransformColumnTypes(Split,{{"UserID.1", Int64.Type}, {"UserID.2", Int64.Type}, {"UserID.3", Int64.Type}}),
    Unpivot = Table.UnpivotOtherColumns(Type, {"ITNL", "PlanonID", "CompanyCode", "OBJ_CompCode", "Role"}, "Attribut", "UserID"),
    Columns = Table.SelectColumns(Unpivot,{"ITNL", "PlanonID", "CompanyCode", "OBJ_CompCode", "Role", "UserID"})
in
    Columns
ManuelBolz's avatar
ManuelBolz
Responsive Resident
2 years ago

Hello DGPBi,

 

Okay, then you need a join with your LocationList table. Here in the following solution I have added you the Customized Code with documentation information.

Your fist Query:

let
    //Example Data
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcgsKCAo2MFLSUfJ38jIwMDY2tgCy3YKCXUJCghxhwmBxsCBYzNjSRClWB49mZ3SNIAEjQ2tjIALpjQUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ITNL = _t, PlanonID = _t, CompanyCode = _t, OBJ_compCode = _t, UserID = _t]),

    //Splitting the User ID
    Split = Table.SplitColumn(Source, "UserID", Splitter.SplitTextByDelimiter(";", QuoteStyle.Csv), {"UserID.1", "UserID.2", "UserID.3"}),

    //Change Datatype
    Type = Table.TransformColumnTypes(Split,{{"UserID.1", Int64.Type}, {"UserID.2", Int64.Type}, {"UserID.3", Int64.Type}}),

    //Unpivot User ID
    Unpivot = Table.UnpivotOtherColumns(Type, {"ITNL", "PlanonID", "CompanyCode", "OBJ_compCode"}, "Attribut", "UserID"),

    //Join with Location List by PlanonID
    Join = Table.NestedJoin(Unpivot, {"PlanonID"}, LocationList, {"PlanonID"}, "LocationList", JoinKind.LeftOuter),

    //Expand Role from Location List
    Expand = Table.ExpandTableColumn(Join, "LocationList", {"Role"}, {"Role"}),

    //Show relevant Columns
    Columns = Table.SelectColumns(Expand,{"ITNL", "PlanonID", "CompanyCode", "OBJ_compCode", "Role", "UserID"})
in
    Columns

 

A second Query with the name "LocationList":

let
    //Sample
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8nfyMjAwNja2UNJRcgryVYqNBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [PlanonID = _t, Role = _t]),

    //Change Datatype
    Datatype = Table.TransformColumnTypes(Source,{{"PlanonID", type text}, {"Role", type text}})
in
    Datatype

 


Best regards from Germany
-Manuel

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.