March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early bird discount ends December 31.
Register NowBe one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch 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
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
User | Count |
---|---|
20 | |
13 | |
10 | |
9 | |
7 |
User | Count |
---|---|
39 | |
27 | |
16 | |
15 | |
10 |