Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
krekandus
Regular Visitor

Merge in Power query editor

Hello, I found one issue in Power Qury editor.

OK I have table 2 columns, USER_ID and NOTICE. in column NOTICE, exit data in following inetrface, {[timestamp] description} and it exist till 20 times separator is "||" double pipeline. I made following, create two same query with start, first have order Ascendnig an second have order descending, Column NOTICE splited with separator ||, get til 20 new columns, thouse columns unpivot and get:

USER_ID, Attribute, Value, in bouth query. Also group by USER_ID, set index in tables, and naow have two tables Attribute Ascending and second Descending. Expanded new tables, set filter INDEX=1. NOw I have in first query USER_ID and first column from NOTICE column, in second USER_ID and last column from NOTICE.

After that made merge first with second, get table when check data in table it looks fine:

krekandus_0-1672403342223.png

After merge expand table I got same values:

krekandus_1-1672403504684.png

 

Is it some issue in Editor or I made something wrong.

Best regards

7 REPLIES 7
v-stephen-msft
Community Support
Community Support

Hi  @krekandus,

  

It may be caused by your matching columns or join kinds.

About matching columns, you can select multiple columns at the same time.

For the choice of kinds to join, you can refer to the following:

vstephenmsft_0-1672641594512.png

Reference:

Merge queries (Power Query) - Microsoft Support

 

 

 

 

Best Regards,

Stephen Tao

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Mahesh0016
Super User
Super User

@krekandus 
Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

Sorry but I can not upload file, send me your mail, or describe how to upload file

@krekandus you may know this allready but in case you don't, it might be easier to split your delimited values into rows prior to merging the columns.

This may help you resolve where the error is being introduced.


If you ensure 'Rows' is selected as shown below that will provide you with a new row for each value.
Split Delimiter in to new rowSplit Delimiter in to new row

Click ok

 

OutputOutput

 

Cheers,
Richard

I made it,

1. Read data from excel

2. create two reference on first step

3. first reference have min value  QMIN

4. second reference hva max value QMAX

5. Merge QMIN Left Outer Join QMAX

6 In new table I get wrigth values from QMAX

This is frist Query:

krekandus_0-1672644857576.png

QMIN:

krekandus_1-1672644935122.png

 

QMAX:

krekandus_2-1672644967462.png

From bouth Query remove last two columns, and Attribute Split with "/" separator and replace QMIN with START and QMAX with END:

QMIN:

krekandus_3-1672645059588.png

 

QMAX:

krekandus_4-1672645073383.png

 

Merge LEft outer JOin

krekandus_5-1672645096055.png

 

First Row tabel values:

 

krekandus_6-1672645182269.png

Everthing is nice with problems.

But After Expand last row, get same Value as from QMIN query:

krekandus_7-1672645316678.png

I schould get from QMAX Value, which is Ok in step before Expand.

I get here same VAlue from QMIN Value column.

 

Bet regards

 

 

How it looks in tables:

krekandus_8-1672645653985.png

 

 

Its wrong after merge, green is ok red is wrong

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.