cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
magus
Helper I
Helper I

Merging to files upon common Value

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:

 

TXT.PNG

 

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

 

 

 

1 ACCEPTED SOLUTION
Icey
Community Support
Community Support

Hi @magus ,

 

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

fill.gif

 

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.

View solution in original post

4 REPLIES 4
Icey
Community Support
Community Support

Hi @magus ,

 

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

fill.gif

 

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.

View solution in original post

NamishB
Post Prodigy
Post Prodigy

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
Super User II
Super User II

@magus,

 

Try the following in Power Query:

 

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

2. In column Customer ID, filter out null.

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:

 

Example.png

 

Hope that I explained myself.

Magus

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

Top Solution Authors
Top Kudoed Authors