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.

florentb's avatar
florentb
Frequent Visitor
6 years ago

Hello to both you and thanks a lot for your answers,

 

edhans 

Sadly I really can't data with you... 

Though, I can tell you that :

  • The join type is "LeftOuter"
  • My join is made on 2 columns whose type is "Text"
  • The column I want to expand also contains "Text" type values
  • I tried to filter out the null values but I get no lines : all the values are null

 

Jimmy801 

I tried your solution but it gives the same result : all expanded columns only contain null values ...

 

I'm quite lost, I don't see how each line can have a table containing the right values after merging (the column I want is "key PDM-SITE") :

 

 

But not after expanding :

Jimmy801's avatar
Jimmy801
Community Champion
6 years ago

Hello florentb 

 

could you please share the M-code from the adavanced editor?

 

BR

 

Jimmy

  • florentb's avatar
    florentb
    Frequent Visitor
    6 years ago

    Hello Jimmy801 ,

     

    There it is :

    let
    Source = Excel.Workbook(File.Contents("\\d1\dfslvm\Applications\HPs diciembre requette.xlsx"), null, true),
    HPs_Sheet = Source{[Item="Hoja1",Kind="Sheet"]}[Data],
    #"Premières lignes supprimées" = Table.Skip(HPs_Sheet,1),
    #"En-têtes promus" = Table.PromoteHeaders(#"Premières lignes supprimées", [PromoteAllScalars=true]),
    #"Type modifié" = Table.TransformColumnTypes(#"En-têtes promus",{{"Branch/Plant (Item Branch File)", Int64.Type}, {"Transaction Quantity (Item Ledger File)", Int64.Type}, {"Trans. Date (Item Ledger File)", type date}}),
    #"Personnalisée ajoutée" = Table.AddColumn(#"Type modifié", "Test non IJ1", each if [#"G/L Class (Item Branch File)"] <> "IJ1" or Text.StartsWith([#"Product No (Item Branch File)"],"H") then 1 else ""),
    #"Lignes vides supprimées" = Table.SelectRows(#"Personnalisée ajoutée", each not List.IsEmpty(List.RemoveMatchingItems(Record.FieldValues(_), {"", null}))),
    #"Valeur remplacée" = Table.ReplaceValue(#"Lignes vides supprimées","MPW","M",Replacer.ReplaceText,{"Type Bill of Material (Work Order Master File)"}),
    #"Index ajouté" = Table.AddIndexColumn(#"Valeur remplacée", "Index", 0, 1),
    #"Duplication de la colonne" = Table.DuplicateColumn(#"Index ajouté", "Product No (Item Branch File)", "SKU"),
    #"Fractionner la colonne par position" = Table.SplitColumn(#"Duplication de la colonne", "SKU", Splitter.SplitTextByPositions({0, 1}, true), {"SKU - Copier.1", "SKU - Copier.2"}),
    #"Type modifié1" = Table.TransformColumnTypes(#"Fractionner la colonne par position",{{"SKU - Copier.1", type text}, {"SKU - Copier.2", type text}, {"Product No (Item Branch File)", type text}}),
    #"Colonne conditionnelle ajoutée" = Table.AddColumn(#"Type modifié1", "Personnalisé", each if [#"SKU - Copier.2"] = "Q" then "" else if [#"SKU - Copier.2"] = "W" then "" else if [#"SKU - Copier.2"] = "E" then "" else if [#"SKU - Copier.2"] = "R" then "" else if [#"SKU - Copier.2"] = "T" then "" else if [#"SKU - Copier.2"] = "Y" then "" else if [#"SKU - Copier.2"] = "U" then "" else if [#"SKU - Copier.2"] = "I" then "" else if [#"SKU - Copier.2"] = "O" then "" else if [#"SKU - Copier.2"] = "P" then "" else if [#"SKU - Copier.2"] = "A" then "" else if [#"SKU - Copier.2"] = "S" then "" else if [#"SKU - Copier.2"] = "D" then "" else if [#"SKU - Copier.2"] = "F" then "" else if [#"SKU - Copier.2"] = "G" then "" else if [#"SKU - Copier.2"] = "H" then "" else if [#"SKU - Copier.2"] = "K" then "" else if [#"SKU - Copier.2"] = "J" then "" else if [#"SKU - Copier.2"] = "L" then "" else if [#"SKU - Copier.2"] = "Z" then "" else if [#"SKU - Copier.2"] = "X" then "" else if [#"SKU - Copier.2"] = "C" then "" else if [#"SKU - Copier.2"] = "V" then "" else if [#"SKU - Copier.2"] = "B" then "" else if [#"SKU - Copier.2"] = "N" then "" else if [#"SKU - Copier.2"] = "M" then "" else [#"SKU - Copier.2"]),
    #"Colonnes fusionnées" = Table.CombineColumns(#"Colonne conditionnelle ajoutée",{"SKU - Copier.1", "Personnalisé"},Combiner.CombineTextByDelimiter("", QuoteStyle.None),"SKU sans taille"),
    #"Colonnes supprimées" = Table.RemoveColumns(#"Colonnes fusionnées",{"SKU - Copier.2", "Test non IJ1 ou Bumbag"}),
    #"Requêtes fusionnées" = Table.NestedJoin(#"Colonnes supprimées", {"SKU sans taille"}, PLM_PRODUCT_SAMPLE, {"ERPCode"}, "PLM_PRODUCT_SAMPLE", JoinKind.LeftOuter),
    #"PLM_PRODUCT_SAMPLE développé" = Table.ExpandTableColumn(#"Requêtes fusionnées", "PLM_PRODUCT_SAMPLE", {"key PDM-SITE"}, {"PLM_PRODUCT_SAMPLE.key PDM-SITE"})
in
    #"PLM_PRODUCT_SAMPLE développé"

     

    Thank you again for your help.

    • Jimmy801's avatar
      Jimmy801
      Community Champion
      6 years ago

      Hello florentb 

       

      could you please share also the query for PLM_PRODUCT_SAMPLE

       

      Jimmy

      • florentb's avatar
        florentb
        Frequent Visitor
        6 years ago

        There it is :

         

        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}}),
    #"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"