Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Merge csv files issue

Hello

I tried to fix a problem with merging two csv files. I'm merging them by using a key column and as a result, want to get a column where rows with equal keys return value and rows where keys are not equal return not null, but previous equal value.


Example

2019-08-21 - campaign_name - 2019-08-21campaign_name - live
2019-08-22 - campaign_name - 2019-08-21campaign_name - null
2019-08-23 - campaign_name - 2019-08-21campaign_name - null
2019-08-24 - campaign_name - 2019-08-21campaign_name - change
2019-08-25 - campaign_name - 2019-08-21campaign_name - null
2019-08-26 - campaign_name - 2019-08-21campaign_name - null

So I need to change all null after live to live and all null after change to change.


Advanced Editor formula

let
    Source = Csv.Document(File.Contents("C:\Users\sergey.grytsuk\Desktop\test - data1.csv"),[Delimiter=",", Columns=3, Encoding=1252, QuoteStyle=QuoteStyle.None]),
    #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"date", type date}, {"campaign", type text}, {"key", type text}}),
    #"Merged Queries" = Table.NestedJoin(#"Changed Type", {"key"}, #"test - data2", {"key"}, "test - data2", JoinKind.LeftOuter),
    #"Expanded test - data2" = Table.ExpandTableColumn(#"Merged Queries", "test - data2", {"cpa_7"}, {"cpa_7"})
in
    #"Expanded test - data2"




 

Need Help
  • Mariusz's avatar
    Mariusz
    6 years ago

    Hi Anonymous 

     

    If you need to fill null values with first available value, simply use Fill > Down in Query editor.

    Please see the method applied in the example below.

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUfLJLEtVitWJVnICcsAMZxjDBchwzkjMS4cocIWJu8EY7mBGLAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Name = _t, Column1 = _t]),
    #"Replaced Value" = Table.ReplaceValue(Source,"",null,Replacer.ReplaceValue,{"Column1"}),
    #"Filled Down" = Table.FillDown(#"Replaced Value",{"Column1"})
in
    #"Filled Down"
    Best Regards,
    Mariusz

    If this post helps, then please consider Accepting it as the solution.

    Please feel free to connect with me.
    Mariusz Repczynski

     

1 Reply

  • Mariusz's avatar
    Mariusz
    Community Champion
    6 years ago

    Hi Anonymous 

     

    If you need to fill null values with first available value, simply use Fill > Down in Query editor.

    Please see the method applied in the example below.

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUfLJLEtVitWJVnICcsAMZxjDBchwzkjMS4cocIWJu8EY7mBGLAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Name = _t, Column1 = _t]),
    #"Replaced Value" = Table.ReplaceValue(Source,"",null,Replacer.ReplaceValue,{"Column1"}),
    #"Filled Down" = Table.FillDown(#"Replaced Value",{"Column1"})
in
    #"Filled Down"
    Best Regards,
    Mariusz

    If this post helps, then please consider Accepting it as the solution.

    Please feel free to connect with me.
    Mariusz Repczynski