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
ManuelBolz's avatar
ManuelBolz
Responsive Resident
2 years ago

Hello DGPBi,

 

you need to UNPIVOT your Data.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcgsKCAo2MFLSUfJ38jIwMDY2tgCy3YKCXUJCghxhwmBxsCBYzCnIF0gaW5ooxergMcIZXbszXK+RobUxEIFMiAUA", 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, Role = _t, UserID = _t]),
    Split = Table.SplitColumn(Source, "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

 

 

 

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.