Concatenate rows from unrelated data sources

[all40]

Hello everyone,

First time poster and new Power BI user, so please excuse me if this is unclear.  I would like to concatenate data from two, unrelated CSV files in Power BI Query Editor and can't figure it out.  

For example, I have two CSV files (Name and Dates).  I have imported them into Power BI as 2 separate data sources.  I would like a new resulting query to be a concatenation of Name for every distinct date in the Dates file.

If my Names dataset consisted of "Jim" and "Bill" and Dates dataset of "1/1", "1/2", and "1/3", I would like the new query to contain "1/1Bill", "1/2Bill", "1/3Bill", "1/1Jim", "1/2Jim", and "1/3Jim".

Any help and/or guidance would be greatly appreciated.  Thank you in advance!

-Alex.

[Rfranca · Accepted solution]

hi, @all40

Alex, 

with advanced querys editor.....

>>>TABLE DATES

let
    Fonte = Excel.Workbook(File.Contents("C:\Users\adm\Desktop\KUNDOO 344888\KUNDOO_25010053.xlsx"), null, true),
    DATES_Sheet = Fonte{[Item="DATES",Kind="Sheet"]}[Data],
    #"Cabeçalhos Promovidos" = Table.PromoteHeaders(DATES_Sheet, [PromoteAllScalars=true]),
    #"Tipo Alterado" = Table.TransformColumnTypes(#"Cabeçalhos Promovidos",{{"Date", type text}})
in
    #"Tipo Alterado"

>>> TABLE NAMES

let
    Fonte = Excel.Workbook(File.Contents("C:\Users\adm\Desktop\KUNDOO 344888\KUNDOO_25010053.xlsx"), null, true),
    NAMES_Sheet = Fonte{[Item="NAMES",Kind="Sheet"]}[Data],
    #"Cabeçalhos Promovidos" = Table.PromoteHeaders(NAMES_Sheet, [PromoteAllScalars=true]),
    #"Tipo Alterado" = Table.TransformColumnTypes(#"Cabeçalhos Promovidos",{{"Names", type text}}),
    #"Tabela Transposta" = Table.Transpose(#"Tipo Alterado")
in
    #"Tabela Transposta"

>>> TABLE MERGE

let
    Fonte = Table.NestedJoin(DATES,{"Date"},NAMES,{"Column1"},"NAMES",JoinKind.FullOuter),
    #"NAMES Expandido" = Table.ExpandTableColumn(Fonte, "NAMES", {"Column1", "Column2", "Column3", "Column4", "Column5", "Column6"}, {"Column1", "Column2", "Column3", "Column4", "Column5", "Column6"}),
    #"Linhas Classificadas" = Table.Sort(#"NAMES Expandido",{{"Column1", Order.Descending}}),
    #"Preenchido Abaixo" = Table.FillDown(#"Linhas Classificadas",{"Column1", "Column2", "Column3", "Column4", "Column5", "Column6"}),
    #"Colunas Não Dinâmicas" = Table.UnpivotOtherColumns(#"Preenchido Abaixo", {"Date"}, "Atributo", "Valor"),
    #"Colunas Removidas" = Table.RemoveColumns(#"Colunas Não Dinâmicas",{"Atributo"}),
    #"Linhas Filtradas" = Table.SelectRows(#"Colunas Removidas", each ([Date] <> null)),
    #"Tipo Alterado" = Table.TransformColumnTypes(#"Linhas Filtradas",{{"Date", type date}}),
    #"Dia Inserido" = Table.AddColumn(#"Tipo Alterado", "Dia", each Date.Day([Date]), Int64.Type),
    #"Mês Inserido" = Table.AddColumn(#"Dia Inserido", "Mês", each Date.Month([Date]), Int64.Type),
    #"Tipo Alterado1" = Table.TransformColumnTypes(#"Mês Inserido",{{"Mês", type text}, {"Dia", type text}}),
    #"Personalização Adicionada" = Table.AddColumn(#"Tipo Alterado1", "Personalizar", each [Mês] &"/"& [Dia] & [Valor]),
    #"Colunas Removidas2" = Table.RemoveColumns(#"Personalização Adicionada",{"Dia", "Mês"}),
    #"Tipo Alterado2" = Table.TransformColumnTypes(#"Colunas Removidas2",{{"Personalizar", type text}, {"Valor", type text}})
in
    #"Tipo Alterado2"

Please try and test using it. And please mark the right reply as answer if your issue has been resolved, otherwise, please feel free to ask if you have any other issue.

Best Regards,
Rfranca