Merging to files upon common Value

[magus]

Hello,

I am using a TXT file as a source data to analyze the information about the Custromers. My unique value is a Customer ID and the rest of the columns are displaying information regarding those customers.

After loading the TXTfile in Power BI I get the file that has the following structure:

Basically I see the information of one Customer ID split in two rows: for example for the first cstr I see the delivery number in the first row of the second column, but the order quantity appears in the second row in the third column. What I need to do is to compress the data from two rows into just one row, so I can see the data for one Cutomer ID displayed in the same row. I hope that I explain myself:)

I will need this report to be updated on a weekly basis so I am trying to find a way to automatize it. What firstly came info my mind was to fill down the Customer ID column, then split the file into two: one with even rows and the second one with not even rows, and then merge these two files with Customer ID. Unfortunately, it does not work - I am not sure why the merging partis not working, but as a result I get one file file with either even or uneven rows. Not the file that combines the two of them.

Could you please help me out how to fix this problem?

Kind regards and thank you in advance

Magus

[Icey · Accepted solution]

Hi @magus ,

Try to add Index columns and join the table with itself:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("pco9DoAgDAXgu7yZoa0t0FWMLk4mDoZw/2sIjK5O7ydfrWBiWTzFjIDjEtK99IYWKkYGmNJcA6qrSP/W01S2+yc0ItLIluOUbqk8H+mK1l4=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Customer ID" = _t, #"Delivery number" = _t, Column1.23 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Customer ID", Int64.Type}, {"Delivery number", type text}, {"Column1.23", Int64.Type}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index0", 0, 1),
    #"Added Index1" = Table.AddIndexColumn(#"Added Index", "Index1", 1, 1),
    #"Merged Queries" = Table.NestedJoin(#"Added Index1", {"Index1"}, #"Added Index1", {"Index0"}, "SecondNextRow", JoinKind.LeftOuter),
    #"Removed Columns" = Table.RemoveColumns(#"Merged Queries",{"Column1.23", "Index0", "Index1"}),
    #"Expanded SecondNextRow" = Table.ExpandTableColumn(#"Removed Columns", "SecondNextRow", {"Column1.23"}, {"Column1.23"}),
    #"Filtered Rows" = Table.SelectRows(#"Expanded SecondNextRow", each ([Customer ID] <> null))
in
    #"Filtered Rows"

Reference: How to shift cells up or down in Power BI?

Best regards

Icey

If this post helps,then consider Accepting it as the solution to help other members find it faster.

[Anonymous]

Hi @magus - I guess the data forwarding in two different columns is because you might have Tab or multiple spaces between the values?

Is there a possibility you can convert you txt file to CSV/Excel and this should fall in one column when loaded to power BI?

Alternative, you can try Transform once in Power Query (Right click Fill Up Column1 and remove null from Customer ID) and and it will automatically do everytime you load the data.

Hope this helps.

Cheers,

-Namish B

[DataInsights]

@magus,

Try the following in Power Query:

1. Select column Column1.23 and "Fill Up".

2. In column Customer ID, filter out null.

[magus]

Hello,

Thank you for sharing your ideas.

Unfortunately this solution did not work, as I have null values in the rown where the information is missing. For instance check the screenshot below, If I use the "fill down" function here, I will get the value 1 not only where it should appear , but also where the information is missing and I should have the null value:

Hope that I explained myself.

Magus