Forum Discussion

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

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

Responsive Resident

2 years ago

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

Helper I

2 years ago

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

Responsive Resident

2 years ago

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.

Forum Discussion

Create a dynamic table based on some colums from another table

Related content

Creating a dynamic slicer

Having trouble creating dynamic goal

Dynamic Filter

Creating a dynamic filtered table variable

Dynamic Subscription