Forum Discussion

florentb's avatar
florentb
Frequent Visitor
6 years ago
Solved

Expand columns doesn't work after Merging queries

Hi all,   First I wanted to thank everybody participating in that community, you're of a great help !   I have a problem when I'm trying to expand a column after merging 2 queries.   The mergin...
Need Help
  • TheDataMustFlow's avatar
    TheDataMustFlow
    6 years ago

    This might be a long shot, but sometimes the issue is simpler than it looks.

    Does any of the columns that you are trying to join have trailing spaces?

     

    Try to trim the columns or transform them using Text.Start and check if this resolves the issue that you're having.

Jimmy801's avatar
Jimmy801
Community Champion
6 years ago

Hello florentb 

 

I've checked your code.

The only thing I can see is that on one query the key-column is created out of 13 characters 

each Text.Start([CodePDM],8) & "-" & Text.Start([Fournisseur],4)),

and on the other query is a complex split of SplitbyPosition {0,1}, applying a change to the second result and reunion afterwards.

I can't really understand how this both key-columns can ever join.

Could you please post a screenshot of both tables before joining.

 

Jimmy

florentb's avatar
florentb
Frequent Visitor
6 years ago

Hello Jimmy801

 

The only thing I can see is that on one query the key-column is created out of 13 characters 

 

each Text.Start([CodePDM],8) & "-" & Text.Start([Fournisseur],4)),

 

Unless I'm mistaken, I'm not joining the other table with that column but with the column "ERPCode".

This column, key PDM-SITE, is the one I want to expand at the end.

 

and on the other query is a complex split of SplitbyPosition {0,1}, applying a change to the second result and reunion afterwards.

I do these transformations because I have a column "SKU" in the table "PLM_PRODUCT_SAMPLE" which is sometimes finishing by a letter. If there is one, I have to remove it in the column "SKU sans taille" to get the "ERPCode". Finally, I want to join the "SKU sans taille" column of the "HPs" table to the "ERPCode" column of the "PLM_PRODUCT_SAMPLE" table.

I know it's a weird transformation haha but it works.

 

In summary, I have :

 

The PLM_PRODUCT_SAMPLE table :

ERPCodekey PDM-SITE...
11110000275-S005 
22220006204-T004 
33330006662-T004 
44440007083-T004 

 

The HPs table :

SKU sans taille......
1111  
1111  
2222  
4444  

 

And I want to have the HPs table like that :

SKU sans taillekey PDM-SITE...
11110000275-S005 
11110000275-S005 
22220006204-T004 
44440007083-T004 

 

  • TheDataMustFlow's avatar
    TheDataMustFlow
    Frequent Visitor
    6 years ago

    This might be a long shot, but sometimes the issue is simpler than it looks.

    Does any of the columns that you are trying to join have trailing spaces?

     

    Try to trim the columns or transform them using Text.Start and check if this resolves the issue that you're having.

  • Jimmy801's avatar
    Jimmy801
    Community Champion
    6 years ago

    Hello florentb

     

    OMG... i completly got it wrong... LOL.. too many thoughts.

    Could you try this code if it works?

    let
    Source = Sql.Database("DLSC-d-PRD-RO", "MSDLSD"),
    dbo_PLM_PRODUCT_SAMPLE = Source{[Schema="dbo",Item="PLM_PRODUCT_SAMPLE"]}[Data],
    #"Lignes filtrées" = Table.SelectRows(dbo_PLM_PRODUCT_SAMPLE, each ([SampleType] = "Affectation couleur                               ") and ([StatutProduction] = "Affecté                                           ")),
    #"Personnalisée ajoutée" = Table.AddColumn(#"Lignes filtrées", "GoProd", each if [FlagGoProdProto]="true" then 1 else 0),
    #"Type modifié" = Table.TransformColumnTypes(#"Personnalisée ajoutée",{{"GoProd", Int64.Type}, {"ERPCode", type text}}),
    #"Personnalisée ajoutée1" = Table.AddColumn(#"Type modifié", "Log", each if [FirstExportOkDate]=null then 0 else 1),
    #"Type modifié1" = Table.TransformColumnTypes(#"Personnalisée ajoutée1",{{"Log", Int64.Type}}),
    #"Personnalisée ajoutée2" = Table.AddColumn(#"Type modifié1", "Site", each Text.AfterDelimiter([Fournisseur],"-")),
    #"Personnalisée ajoutée3" = Table.AddColumn(#"Personnalisée ajoutée2", "key PDM-SITE", each Text.Start([CodePDM],8) & "-" & Text.Start([Fournisseur],4)),
    #"Valeur remplacée" = Table.ReplaceValue(#"Personnalisée ajoutée3"," ","",Replacer.ReplaceText,{"CodePDM"}),
    #"Requêtes fusionnées" = Table.NestedJoin(#"Valeur remplacée", {"CodePDM"}, #"PLM_STYLE_DETAIL (2)", {"StyleCode"}, "PLM_STYLE_DETAIL (2)", JoinKind.LeftOuter),
    #"PLM_STYLE_DETAIL (2) développé" = Table.ExpandTableColumn(#"Requêtes fusionnées", "PLM_STYLE_DETAIL (2)", {"Siteleader"}, {"Siteleader"}),
    #"Personnalisée ajoutée4" = Table.AddColumn(#"PLM_STYLE_DETAIL (2) développé", "Leader?", each if Text.Start([Fournisseur],4)=Text.Start([Siteleader],4) then true else null)
in
    #"Personnalisée ajoutée4"

     

    All the best

     

    Jimmy

  • florentb's avatar
    florentb
    Frequent Visitor
    6 years ago

    Hello,

     

    Jimmy801 wrote:

    Hello florentb

     

    OMG... i completly got it wrong... LOL.. too many thoughts.

    Could you try this code if it works?

     

    let
    Source = Sql.Database("DLSC-d-PRD-RO", "MSDLSD"),
    dbo_PLM_PRODUCT_SAMPLE = Source{[Schema="dbo",Item="PLM_PRODUCT_SAMPLE"]}[Data],
    #"Lignes filtrées" = Table.SelectRows(dbo_PLM_PRODUCT_SAMPLE, each ([SampleType] = "Affectation couleur                               ") and ([StatutProduction] = "Affecté                                           ")),
    #"Personnalisée ajoutée" = Table.AddColumn(#"Lignes filtrées", "GoProd", each if [FlagGoProdProto]="true" then 1 else 0),
    #"Type modifié" = Table.TransformColumnTypes(#"Personnalisée ajoutée",{{"GoProd", Int64.Type}, {"ERPCode", type text}}),
    #"Personnalisée ajoutée1" = Table.AddColumn(#"Type modifié", "Log", each if [FirstExportOkDate]=null then 0 else 1),
    #"Type modifié1" = Table.TransformColumnTypes(#"Personnalisée ajoutée1",{{"Log", Int64.Type}}),
    #"Personnalisée ajoutée2" = Table.AddColumn(#"Type modifié1", "Site", each Text.AfterDelimiter([Fournisseur],"-")),
    #"Personnalisée ajoutée3" = Table.AddColumn(#"Personnalisée ajoutée2", "key PDM-SITE", each Text.Start([CodePDM],8) & "-" & Text.Start([Fournisseur],4)),
    #"Valeur remplacée" = Table.ReplaceValue(#"Personnalisée ajoutée3"," ","",Replacer.ReplaceText,{"CodePDM"}),
    #"Requêtes fusionnées" = Table.NestedJoin(#"Valeur remplacée", {"CodePDM"}, #"PLM_STYLE_DETAIL (2)", {"StyleCode"}, "PLM_STYLE_DETAIL (2)", JoinKind.LeftOuter),
    #"PLM_STYLE_DETAIL (2) développé" = Table.ExpandTableColumn(#"Requêtes fusionnées", "PLM_STYLE_DETAIL (2)", {"Siteleader"}, {"Siteleader"}),
    #"Personnalisée ajoutée4" = Table.AddColumn(#"PLM_STYLE_DETAIL (2) développé", "Leader?", each if Text.Start([Fournisseur],4)=Text.Start([Siteleader],4) then true else null)
in
    #"Personnalisée ajoutée4"

     

     

    All the best

     

    Jimmy

    Hahaha no problem !

    I tried it and it doesn't work ....

     

    TheDataMustFlow wrote:

    This might be a long shot, but sometimes the issue is simpler than it looks.

    Does any of the columns that you are trying to join have trailing spaces?

     

    Try to trim the columns or transform them using Text.Start and check if this resolves the issue that you're having.

    It wooooorked !!! 🎉

    The devil is really in the detail, I just replaced all " " by "" and now it's ok.

     

    Many thanks to all of you !

  • dave_plasencia's avatar
    dave_plasencia
    New Member
    3 years ago

    I noticed this in my solution. What fixed the issue for me was I was trying to use a dataverse lookup column and filtered down to just the display column. It kept going null at the end of all steps. After having both the column and display column, the value no longer reverted to null.

     

    Hope this helps.