Create a dynamic table based on some colums from another table

[DGPBi]

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 if you prefer.
So, it works fine, but the column UserID can have this kind of result : 21;31;394 or just 21.
What I want is to generate a record by number. For example 

Current DAX:

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])
VAR T2 = SELECTCOLUMNS(LocationList, "ITNL", LocationList[Title],"PlanonID", LocationList[PlanonID], "CompanyCode",LocationList[CompanyCode],"OBJ_CompCode",LocationList[OBJ_CompCode],"Role", "Business Contact", "UserID", LocationList[BusinessContact])
VAR T3 = SELECTCOLUMNS(LocationList, "ITNL", LocationList[Title],"PlanonID", LocationList[PlanonID], "CompanyCode",LocationList[CompanyCode],"OBJ_CompCode",LocationList[OBJ_CompCode],"Role", "IT Site Support", "UserID", LocationList[ITSiteCoordinator])
VAR T4 = SELECTCOLUMNS(LocationList, "ITNL", LocationList[Title],"PlanonID", LocationList[PlanonID], "CompanyCode",LocationList[CompanyCode],"OBJ_CompCode",LocationList[OBJ_CompCode],"Role", "IT Site Support Backup", "UserID", LocationList[ITSiteCoordBackup])
VAR T5 = SELECTCOLUMNS(LocationList, "ITNL", LocationList[Title],"PlanonID", LocationList[PlanonID], "CompanyCode",LocationList[CompanyCode],"OBJ_CompCode",LocationList[OBJ_CompCode],"Role", "Local Contact", "UserID", LocationList[LocalContact])
VAR T6 = SELECTCOLUMNS(LocationList, "ITNL", LocationList[Title],"PlanonID", LocationList[PlanonID], "CompanyCode",LocationList[CompanyCode],"OBJ_CompCode",LocationList[OBJ_CompCode],"Role", "Shipping Contact Contact", "UserID", LocationList[ShippingContact])
VAR T7 = SELECTCOLUMNS(LocationList, "ITNL", LocationList[Title],"PlanonID", LocationList[PlanonID], "CompanyCode",LocationList[CompanyCode],"OBJ_CompCode",LocationList[OBJ_CompCode],"Role", "Site Support Manager", "UserID", LocationList[SiteSupportManager])
RETURN
UNION(T1,T2,T3,T4,T5,T6,T7)

 The result is:

But I would like to have 1 line per UserID like :

Can you help me please ?

[DGPBi]

Hi Manuel,
I don't understood your second answer but with your first answer, I was able to manage the column names.
in your script You create a column name "Attribut" which have the column names but you removed it in your script.
So I just deleted the line in your script.

    Columns = Table.SelectColumns(Unpivot,{"ITNL", "PlanonID", "CompanyCode", "OBJ_compCode", "Role", "UserID"})

And made some changes in the values and we obtain:

[ManuelBolz]

Okay @DGPBi, i hope I could help you. Your query still needs a few optimizations but otherwise the result looks pretty good.

In my second answer I created an additional list (LocationList) which is joined in the first query. You mentioned that this information comes from a second table.

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.

[DGPBi]

See my last version of code. DO you think I can optimize it more ?

let
    Source = LocationList,
    #"Split BRM" = Table.SplitColumn(Source, "Site BRM contactStringId", Splitter.SplitTextByDelimiter(";", QuoteStyle.Csv), {"Site BRM contactStringId.1", "Site BRM contactStringId.2", "Site BRM contactStringId.3"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Split BRM",{{"Title", type text}, {"PlanonID", type text}, {"CompanyCode", type text}, {"Site BRM contactStringId.1", Int64.Type}, {"Site BRM contactStringId.2", Int64.Type}, {"Site BRM contactStringId.3", Int64.Type}, {"BusinessContact", type text}, {"ShippingContact", type text}, {"ITSiteCoordinator", Int64.Type}, {"ITSiteCoordBackup", Int64.Type}, {"LocalContact", Int64.Type}, {"SiteSupportManager", Int64.Type}}),
    #"Split BusinessContact" = Table.SplitColumn(#"Changed Type", "BusinessContact", Splitter.SplitTextByDelimiter(";", QuoteStyle.Csv), {"BusinessContact.1", "BusinessContact.2", "BusinessContact.3"}),
    #"Split ShippingContact" = Table.SplitColumn(#"Split BusinessContact", "ShippingContact", Splitter.SplitTextByDelimiter(";", QuoteStyle.Csv), {"ShippingContact.1", "ShippingContact.2", "ShippingContact.3"}),
    #"Split ITSiteCoordinator" = Table.SplitColumn(Table.TransformColumnTypes(#"Split ShippingContact", {{"ITSiteCoordinator", type text}}, "fr-FR"), "ITSiteCoordinator", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"ITSiteCoordinator.1", "ITSiteCoordinator.2", "ITSiteCoordinator.3"}),
    #"Split ITSiteCoordBackup" = Table.SplitColumn(Table.TransformColumnTypes(#"Split ITSiteCoordinator", {{"ITSiteCoordBackup", type text}}, "fr-FR"), "ITSiteCoordBackup", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"ITSiteCoordBackup.1", "ITSiteCoordBackup.2", "ITSiteCoordBackup.3"}),
    #"Split LocalContact" = Table.SplitColumn(Table.TransformColumnTypes(#"Split ITSiteCoordBackup", {{"LocalContact", type text}}, "fr-FR"), "LocalContact", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"LocalContact.1", "LocalContact.2", "LocalContact.3"}),
    #"Split SiteSupportManager" = Table.SplitColumn(Table.TransformColumnTypes(#"Split LocalContact", {{"SiteSupportManager", type text}}, "fr-FR"), "SiteSupportManager", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"SiteSupportManager.1", "SiteSupportManager.2", "SiteSupportManager.3"}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Split SiteSupportManager", {"Title", "PlanonID", "CompanyCode", "OBJ_CompCode"}, "Attribute", "Value"),
    #"Renamed Columns" = Table.RenameColumns(#"Unpivoted Other Columns",{{"Attribute", "Role"}, {"Value", "UserID"}}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Renamed Columns",{{"UserID", Int64.Type}}),
    #"Replaced Value" = Table.ReplaceValue(#"Changed Type1","Site BRM contactStringId","Business Relationship Manager",Replacer.ReplaceText,{"Role"}),
    #"Replaced Value2" = Table.ReplaceValue(#"Replaced Value","BusinessContact","Business contact",Replacer.ReplaceText,{"Role"}),
    #"Replaced Value3" = Table.ReplaceValue(#"Replaced Value2","ShippingContact","Shipping Contact",Replacer.ReplaceText,{"Role"}),
    #"Replaced Value4" = Table.ReplaceValue(#"Replaced Value3","ITSiteCoordinator","IT Site Support",Replacer.ReplaceText,{"Role"}),
    #"Replaced Value5" = Table.ReplaceValue(#"Replaced Value4","ITSiteCoordBackup.1","IT Site Support Backup",Replacer.ReplaceText,{"Role"}),
    #"Replaced Value6" = Table.ReplaceValue(#"Replaced Value5","LocalContact","Local contact",Replacer.ReplaceText,{"Role"}),
    #"Replaced Value7" = Table.ReplaceValue(#"Replaced Value6","SiteSupportManager.1","IT Site Support Manager",Replacer.ReplaceText,{"Role"})
in
    #"Replaced Value7"

[ManuelBolz]

Hello @DGPBi,

Much better. I'm a fan of CamelCase notation in the context of M-Code. I think this makes the code much more readable.

let
    Source = LocationList,
    SplitBRM = Table.SplitColumn(Source, "Site BRM contactStringId", Splitter.SplitTextByDelimiter(";", QuoteStyle.Csv), {"Site BRM contactStringId.1", "Site BRM contactStringId.2", "Site BRM contactStringId.3"}),
    ChangedType = Table.TransformColumnTypes(SplitBRM,{{"Title", type text}, {"PlanonID", type text}, {"CompanyCode", type text}, {"Site BRM contactStringId.1", Int64.Type}, {"Site BRM contactStringId.2", Int64.Type}, {"Site BRM contactStringId.3", Int64.Type}, {"BusinessContact", type text}, {"ShippingContact", type text}, {"ITSiteCoordinator", Int64.Type}, {"ITSiteCoordBackup", Int64.Type}, {"LocalContact", Int64.Type}, {"SiteSupportManager", Int64.Type}}),
    SplitBusinessContact = Table.SplitColumn(ChangedType, "BusinessContact", Splitter.SplitTextByDelimiter(";", QuoteStyle.Csv), {"BusinessContact.1", "BusinessContact.2", "BusinessContact.3"}),
    SplitShippingContact = Table.SplitColumn(SplitBusinessContact, "ShippingContact", Splitter.SplitTextByDelimiter(";", QuoteStyle.Csv), {"ShippingContact.1", "ShippingContact.2", "ShippingContact.3"}),
    SplitITSiteCoordinator = Table.SplitColumn(Table.TransformColumnTypes(SplitShippingContact, {{"ITSiteCoordinator", type text}}, "fr-FR"), "ITSiteCoordinator", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"ITSiteCoordinator.1", "ITSiteCoordinator.2", "ITSiteCoordinator.3"}),
    SplitITSiteCoordBackup = Table.SplitColumn(Table.TransformColumnTypes(SplitITSiteCoordinator, {{"ITSiteCoordBackup", type text}}, "fr-FR"), "ITSiteCoordBackup", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"ITSiteCoordBackup.1", "ITSiteCoordBackup.2", "ITSiteCoordBackup.3"}),
    SplitLocalContact = Table.SplitColumn(Table.TransformColumnTypes(SplitITSiteCoordBackup, {{"LocalContact", type text}}, "fr-FR"), "LocalContact", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"LocalContact.1", "LocalContact.2", "LocalContact.3"}),
    SplitSiteSupportManager = Table.SplitColumn(Table.TransformColumnTypes(SplitLocalContact, {{"SiteSupportManager", type text}}, "fr-FR"), "SiteSupportManager", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"SiteSupportManager.1", "SiteSupportManager.2", "SiteSupportManager.3"}),
    Unpivoted = Table.UnpivotOtherColumns(SplitSiteSupportManager, {"Title", "PlanonID", "CompanyCode", "OBJ_CompCode"}, "Attribute", "Value"),
    Rename = Table.RenameColumns(Unpivoted,{{"Attribute", "Role"}, {"Value", "UserID"}}),
    ChangedType1 = Table.TransformColumnTypes(Rename,{{"UserID", Int64.Type}}),
    ReplacedValue = Table.ReplaceValue(ChangedType1,"Site BRM contactStringId","Business Relationship Manager",Replacer.ReplaceText,{"Role"}),
    ReplacedValue2 = Table.ReplaceValue(ReplacedValue,"BusinessContact","Business contact",Replacer.ReplaceText,{"Role"}),
    ReplacedValue3 = Table.ReplaceValue(ReplacedValue2,"ShippingContact","Shipping Contact",Replacer.ReplaceText,{"Role"}),
    ReplacedValue4 = Table.ReplaceValue(ReplacedValue3,"ITSiteCoordinator","IT Site Support",Replacer.ReplaceText,{"Role"}),
    ReplacedValue5 = Table.ReplaceValue(ReplacedValue4,"ITSiteCoordBackup.1","IT Site Support Backup",Replacer.ReplaceText,{"Role"}),
    ReplacedValue6 = Table.ReplaceValue(ReplacedValue5,"LocalContact","Local contact",Replacer.ReplaceText,{"Role"}),
    ReplacedValue7 = Table.ReplaceValue(ReplacedValue6,"SiteSupportManager.1","IT Site Support Manager",Replacer.ReplaceText,{"Role"})
in
    ReplacedValue7

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.

[DGPBi · Accepted solution]

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]

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.

[ManuelBolz]

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.