Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
Hello,
I have a table in Power query which looks like this:
Code | Delivery Phase | Approval Date | Advance | Invoice | Advanced Payment |
24008 | Deellevering1 | 17/05/2024 | 1950 | 20803,6 | 23/01/2024 |
24008 | Deellevering2 | 21/06/2024 | 6409,47 | ||
24006 | Deellevering1 | 21/06/2024 | 2490 | 28809,56 | 23/01/2024 |
Code | Delivery Phase | Type Approval | Approval Date | Invoice |
24008 | Deellevering1 | Invoice | 17/05/2024 | 20803,6 |
24008 | Deellevering1 | Advance | 23/01/2024 | 1950 |
24008 | Deellevering2 | Invoice | 21/06/2024 | 6409,47 |
24006 | Deellevering1 | Invoice | 21/06/2024 | 28809,56 |
24006 | Deellevering1 | Advance | 23/01/2024 | 2490 |
I have tried using the List.Repeat but I didn't go any further as my experience in Power Query is basic.
Thanks in advance!
Kind regards
Solved! Go to Solution.
let
Source = your_table,
f = (x) => {{x{0}, x{1}, "Invoice", x{2}, x{4}}} &
(if x{5} is null then {} else {{x{0}, x{1}, "Advance", x{5}, x{3}}}),
result = Table.FromRows(
List.Combine(Table.ToList(Source, f)),
{"Code", "Delivery Phase", "Type Approval", "Approval Date", "Invoice"}
)
in
result
let
Source = your_table,
f = (x) => {{x{0}, x{1}, "Invoice", x{2}, x{4}}} &
(if x{5} is null then {} else {{x{0}, x{1}, "Advance", x{5}, x{3}}}),
result = Table.FromRows(
List.Combine(Table.ToList(Source, f)),
{"Code", "Delivery Phase", "Type Approval", "Approval Date", "Invoice"}
)
in
result
Hello,
Thanks for the quick response.
This already seems pretty difficult to me. It works with my example but I was wondering:
Thanks!
Kind regards
Hello, @naelske_cronos
1. "x" is a list. List of row values. That's how nice little Table.ToList works.
2. Numbers in {} stand for positions of values in "x" list.
3. If you want to work with column names then maybe Table.ToRecords + List.Transform (or List.Generate) and Table.FromRecords afterwards.
Hello,
Thanks for the help getting started.
I will check how I can use column names instead of positions.
Kind regards
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
33 | |
16 | |
12 | |
11 | |
9 |
User | Count |
---|---|
44 | |
24 | |
16 | |
14 | |
14 |